CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2005base64
Hadi BahrbegiK
Hadi BahrbegiK

Reputation: 13

How to decode base64 unicode string with T-SQL

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?

Upvotes: 1

Views: 10567

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5694

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)'))

Upvotes: 2

Related Questions