Base64 String Encoding C# vs TSQL

Question

I have base64 string encoding/decoding functions in C# and TSQL, my problem is encoded result from C# differs from encoding result in TSQL.

My goal is:

C# encoded string should be decoded in TSQL and vice-versa
Both functions should have an ability to encode/decode unicode characters

C#

Convert.ToBase64String(Encoding.UTF8.GetBytes("test"));

Result: dGVzdA==

TSQL

SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(MAX)')   Base64Encoding
FROM 
(
    SELECT CAST(N'test' AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp

Result: dABlAHMAdAA=

Any idea how to match the results?

Andrew Morton · Accepted Answer · 2017-11-02 09:46:13Z

6

SQL Server is using UTF-16. Your C# code is using UTF-8, hence the difference.

To get the C# code to use UTF-16, you can do this:

Convert.ToBase64String(Encoding.Unicode.GetBytes("test"))

Result: dABlAHMAdAA=

answered Nov 2, 2017 at 9:46

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Samteladze Over a year ago

Thank you for explanation, your solution worked as expected and now I have clear understanding of why it works well.

ChiragMM · Accepted Answer · 2017-11-02 09:47:55Z

1

To get your desire result Use below TSQL

SELECT CAST('test' as varbinary(max)) FOR XML PATH(''), BINARY BASE64

answered Nov 2, 2017 at 9:47

ChiragMM

3474 silver badges13 bronze badges

2 Comments

Evk Over a year ago

Except 'test' is not unicode. If you change that to N'test' - you will again get the same result as in question.

Andrew Morton Over a year ago

@ChiragMM From the question: "My goal is: [...] Both functions should have an ability to encode/decode unicode characters," so it looks like it must be Unicode.

Collectives™ on Stack Overflow

Base64 String Encoding C# vs TSQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related