Udostępnij za pośrednictwem

SQL Base64 conversion

  • Artykuł

Someone asked the question “Using T-SQL only, is there a way to decode Base64 data” and Peter Kalbach replied with what I thought was an elegant answer my implementation of which is shared bellow. The XML capabilities introduced in SQL Server 2005 opens up a broad range of functionality that goes well beyond its directly intended application and this example reminded me that we need to keep our minds open to some lateral application of these technologies.

   DECLARE @str VARCHAR(max), @xml xml, @bin varbinary(max)
  SET @str = 'RG8gbm90IGZvcmdldCB0aGUgU1FMIFNlcnZlciBVc2V
              yIEdyb3VwIG1lZXRpbmcgb24gdGhlIDNyZCBUdWVzZG
              F5IG9mIGV2ZXJ5IG1vbnRoLiA8R2FyeT4='
  SELECT @xml = CAST( '<row data="' + @Str + '" />' as xml )
  SELECT @bin = @xml.value('(/row/@data)[1]', 'varbinary(max)')
  SELECT @str = CAST(@bin as varchar(max))
  SELECT @str

I have also embedded a “secret” reminder for all of you in this code.

To encode a Base64 data the reverse logic applies as implemented here:

   DECLARE @str VARCHAR(max), @xml xml, @bin varbinary(max)
  SET @str = 'This is the new message to encode. <Gary>'
  SELECT @bin = CAST(@str as varbinary(max))
  SET @xml = (SELECT * FROM (SELECT @bin as data) as row FOR XML AUTO,  BINARY BASE64)
  SELECT @str = @xml.value('(/row/@data)[1]', 'varchar(max)')
  SELECT @str

<Gary>

Comments

  • Anonymous
    February 25, 2009
    As a follow-up to my previous post I thought I would have a look at the performance of this type of conversion