SQL 2005 BASE64 Decode Query Syntax

4 Responses to “SQL 2005 BASE64 Decode Query Syntax”

1. 
   muti2
   September 6th, 2008 at 2:29 am

   What if input is in nvarchar?

2. 
   admin
   September 8th, 2008 at 3:55 pm

   nvarchar is fine.
   DECLARE @data nvarchar(max)

3. 
   muti2
   September 8th, 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=

4. 
   admin
   September 18th, 2008 at 2:21 am

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

   JiMxMDQ0OyYjMTA4NjsmIzEwNzM7JiMxMDg4OyYjMTA4Njs

   The result I got is

   &#_1044; &#_1086;&#_1073; &#_1088;&#_1086;

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

Leave a Reply