SQL 2016 - Base64 encoding a SHA256 hashed string

Question

I have the following SQL select query which hashes a user ID using SHA256. I would like to further encode this hash to base64 so that it is easily readable by other applications such as Excel, which right now causes issues when parsing the hashed user ID's. How can I achieve this?

Select
cast(HASHBYTES('SHA2_256', cast(Users.UserID as nvarchar(10))) as varchar(50)) as UserID 
FROM UserTable

Have you seen [this question/answer](https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql)? — HoneyBadger, Jan 02 '18 at 12:52
@HoneyBadger thanks, I did see this but I am not sure how to apply it to my SHA256 hash — blizz, Jan 02 '18 at 15:38
Does this answer your question? [Base64 encoding in SQL Server 2005 T-SQL](https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql) — Ian Kemp, Sep 08 '21 at 10:43

score 2 · Answer 1 · answered Jan 02 '18 at 15:50

The following works for me, the whole XML parsing/hacking requires a VARBINARY:

SELECT  CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("UserID")))', 'VARCHAR(MAX)') Base64Encoding
FROM    (
            SELECT HASHBYTES('SHA2_256', CAST(Users.UserID AS NVARCHAR(10))) AS UserID
            FROM UserTable
        ) a

(Blatantly adapted from this answer)

SQL 2016 - Base64 encoding a SHA256 hashed string

1 Answers1