How to decode base64 unicode string with T-SQL

Question

Can't decode Arabic characters in base64 string. When I decode it must be like this : 'سلام جیران' (using base64encode website). I try to decode like this :

SELECT CAST(
          CAST(N'' AS XML).value('xs:base64Binary("2LPZhNin2YUg2KzbjNix2KfZhg==")' , 'VARBINARY(MAX)') 
          AS NVARCHAR(MAX)
           ) UnicodeEncoding ;

Based on this answer : Base64 encoding in SQL Server 2005 T-SQL But have response like this : '돘蓙Ꟙ藙��' Is there any way to decode Arabic characters correctly?

The problem is that your binary data is a UTF-8 encoding. But SQL Server nvarchar is UCS-2 — Martin Smith, May 19 '18 at 13:07
Thank you for the answer. But is there any solution for this situation? Consider that I use a library that encode messages with UTF-8 then I send them for MS SQL Server — Hadi BahrbegiK, May 19 '18 at 15:09
There is some good information in this answer: https://stackoverflow.com/a/32859598/9395740 — MJH, May 19 '18 at 15:35

score 2 · Accepted Answer · answered May 20 '18 at 09:33

To convert a UTF-8 string to nvarchar, you can use a CLR function (such as this one: https://stackoverflow.com/a/14041069/1187211) or a T-SQL function (such as this one: https://stackoverflow.com/a/28412587/1187211).

Using the second function, you can decode your data like this:

SELECT dbo.DecodeUTF8String(CAST(N'' AS XML).value
('xs:base64Binary("2LPZhNin2YUg2KzbjNix2KfZhg==")','VARBINARY(MAX)'))

How to decode base64 unicode string with T-SQL

1 Answers1

Linked