SQL 2005 BASE64 Decode Query Syntax
Decode BASE64 encoded string SQL query syntax
-- declare vars.
DECLARE @data varchar(max), @XmlData xml
-- set the base64 encoded varchar.
SET @data = 'SGVsbG8gV29ybGQgIQ=='
-- construct an xml var.
SET @XmlData = CAST('<data>' + @data + '</data>' as xml)
-- base64 decode the @data.
SELECT CONVERT(varchar(max),
@XmlData.value('(data)[1]', 'varbinary(max)'))
11 Responses to “SQL 2005 BASE64 Decode Query Syntax”
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!
Comment from muti2
Time September 6, 2008 at 2:29 am
What if input is in nvarchar?