Comment from muti2
Time September 6, 2008 at 2:29 am

What if input is in nvarchar?

Comment from admin
Time September 8, 2008 at 3:55 pm

nvarchar is fine.
DECLARE @data nvarchar(max)

Comment from muti2
Time September 8, 2008 at 8:23 pm

Unfortunatelly not.
This code:
DECLARE @data nvarchar(max), @XmlData xml

SET @data = N’Добро’

SET @XmlData = CAST(” + @data + ” as xml)

SELECT CONVERT(nvarchar(max), @XmlData.value(‘(data)[1]’, ‘varbinary(max)’))

returns NULL.
If i encode this with http://makcoder.sourceforge.net/demo/base64.php
then text is: JiMxMDQ0OyYjMTA4NjsmIzEwNzM7JiMxMDg4OyYjMTA4Njs=

Comment from admin
Time September 18, 2008 at 2:21 am

Hi muti2,
The @data should be set as encoded, in which case, it is

The result I got is

(replace “_” with “”) which is as same as ’Добро’

Try this,

DECLARE @data NVARCHAR(MAX), @XmlData XML

SET @data = N'JiMxMDQ0OyYjMTA4NjsmIzEwNzM7JiMxMDg4OyYjMTA4Njs=' 

SET @XmlData = CAST('<data>' + @data + '</data>' AS XML)

SELECT CONVERT(VARCHAR(MAX), @XmlData.VALUE('(data)[1]', 'varbinary(max)'))

Comment from dkumar
Time February 25, 2009 at 6:12 am

Looks like this breaks for certain cases.
example
original string “h9l+€*c”
base64encoded “aDlsK+KCrCrCgWM=”

DECLARE @xml xml
DECLARE @binary varbinary(max)
SET @xml = CAST(” AS xml)
SET @binary = (SELECT @xml.value(‘(/row/@mystring)[1]’,’varbinary(max)’))
SELECT CONVERT(varchar(max), @binary)

returns “h9l+€*c” which is not the same as the original string.

Any idea.

Thanks in advance.

Comment from codemeit
Time February 25, 2009 at 4:27 pm

Hi dkumar,
What tools did you use to get base64 encode of “h9l+€*c”,
did you use UTF-8 encoding?
You can try this tool to get the base64 encoded string.
http://www.opinionatedgeek.com/dotnet/tools/Base64Encode/Default.aspx

Run the follow SQL to see, a properly encoded string
matches its original string after decoding.

-- declare vars.

DECLARE @originalData varchar(max)

DECLARE @encodedData varchar(max)

DECLARE @decodedData varchar(max) 

DECLARE @xmlData XML



SET @originalData = 'h9l+€*c'



-- this is my base 64 encoded string with UTF-8

SET @encodedData = 'aDlsK4AqgWM=' 



-- construct an xml var.

SET @xmlData = CAST('<data>' + @encodedData + '</data>' as xml)



-- base64 decode the @encodedData.

SELECT @decodedData = CONVERT(varchar(MAX),  @xmlData.value('(data)[1]', 'varbinary(max)'))



-- generate the outputs

SELECT @decodedData as DecodedAs



IF @decodedData = @originalData

        SELECT 'Good, @decodedData = @originalData'

ELSE 

        SELECT 'Err, not the same.'







-----Output-------------------------------



DecodedAs

h9l+€*c



"Good, @decodedData = @originalData"

-----Output-------------------------------

Comment from dkumar
Time February 26, 2009 at 2:11 am

Thanks for your quick response.

Yes it was base64 encoded in a c# app with UTF-8 encoding.
string base64Encoded = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(originalString));

I was trying different combination and found that
System.Text.Encoding.Default works fine for sql decoding.

so this is what I am using now,

string base64Encoded = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(originalString));

Not sure why my utf8 is returning a different value for certain cases.

Hopefully I will not have any more issues 🙂

Apperciate your help.
Thanks Again

Comment from umesh patidar
Time August 15, 2009 at 9:42 pm

I have try the same above code but not working fine.
they are returning garbage data when decoding utf-8 data.
Original text=????? ???????
UTF-encode String=2LnYqdir2LPYpyDYrdi02YHZh9mK2LTZgg==
above code decode output=عةثسا حشفهيشق

I have not clear why the garbage data returning.

Comment from johan
Time August 19, 2009 at 1:40 am

I am facing the same proble.

Comment from boriss
Time August 27, 2009 at 1:53 am

It works in SQL, Thank You.

Comment from izaltsman
Time September 5, 2009 at 9:40 am

Of all the options for implementing base64 decoding in SQL, this is by far the easiest! Works great! Thanks!