Base64 encoding in SQL Server 2005 T-SQL

Question

I d like to write a T-SQL query where I encode a string as a Base64 string   Surprisingly  I can t find any native T-SQL functions for doing Base64 encoding   Does a native function exist   If not  what s the best way to do Base64 encoding in T-SQL

User · Answer

Pulling in things from all the answers above, here's what I came up with.

There are essentially two ways to do this:

;WITH TMP AS (
    SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)') as  Base64Encoding
    FROM 
    (
        SELECT TOP 10000 CAST(Words AS VARBINARY(MAX)) AS bin FROM TestData
    ) SRC
) 

SELECT *, CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:column("Base64Encoding"))', 'VARBINARY(MAX)') AS NVARCHAR(MAX)) as ASCIIEncoding
FROM
(
    SELECT * FROM TMP
) SRC

And the second way

;WITH TMP AS 
(
    SELECT TOP 10000 CONVERT(VARCHAR(MAX), (SELECT CAST(Wordsas varbinary(max)) FOR XML PATH(''))) as BX
    FROM TestData
)

SELECT *, CONVERT(NVARCHAR(MAX), CONVERT(XML, BX).value('.','varbinary(max)'))
FROM TMP

When comparing performance, the first one has a subtree cost of 2.4414 and the second one has a subtree cost of 4.1538. Which means the first one is about twice as fast as the second one (which is expected, since it uses XML, which is notoriously slow).

User · Answer

I loved  Slai s answer  I only had to make very minor modifications into the one-liners I was looking for  I thought I d share what I ended up with in case it helps anyone else stumbling onto this page like I did   DECLARE  Source VARCHAR 50     12345  DECLARE  Encoded VARCHAR 500    CONVERT VARCHAR 500    SELECT CONVERT VARBINARY   Source  FOR XML PATH      BINARY BASE64   DECLARE  Decoded VARCHAR 500    CONVERT VARCHAR 500   CONVERT XML   Encoded  value      varbinary max     SELECT  Source AS  Source    Encoded AS  Encoded    Decoded AS  Decoded

User · Answer

DECLARE  source varbinary max      encoded base64 varchar max      decoded varbinary max   SET  source   CONVERT varbinary max    welcome    -- Convert from varbinary to base64 string  SET  encoded base64   CAST N   AS xml  value  xs base64Binary sql variable           source       varchar max       -- Convert back from base64 to varbinary     SET  decoded   CAST N   AS xml  value  xs base64Binary sql variable                   encoded base64       varbinary max       SELECT   CONVERT varchar max    source  AS  Source varchar        source AS  Source varbinary          encoded base64 AS  Encoded base64          decoded AS  Decoded varbinary         CONVERT varchar max    decoded  AS  Decoded varchar    This is usefull for encode and decode   By Bharat J

User · Answer

You can use just   Declare  pass2 binary 32  Set  pass2  0x4D006A00450034004E0071006B00350000000000000000000000000000000000 SELECT CONVERT NVARCHAR 16    pass2    then after encoding you ll receive text  MjE4Nqk5

User · Answer

The simplest and shortest way I could find for SQL Server 2012 and above is BINARY BASE64    SELECT CAST  string  as varbinary max   FOR XML PATH      BINARY BASE64   For Base64 to string  SELECT CAST  CAST   c3RyaW5n  as XML   value      varbinary max    AS varchar max        or nvarchar max  for Unicode strings

User · Answer

Here s a modification to mercurial s answer that uses the subquery on the decode as well  allowing the use of variables in both instances   DECLARE      EncodeIn VARCHAR 100     Test String In        EncodeOut VARCHAR 500        DecodeOut VARCHAR 200       SELECT  EncodeOut        CAST N   AS XML  value             xs base64Binary xs hexBinary sql column  bin                 VARCHAR MAX         FROM       SELECT CAST  EncodeIn AS VARBINARY MAX   AS bin   AS bin sql server temp   PRINT  EncodeOut  SELECT  DecodeOut    CAST      CAST N   AS XML  value           xs base64Binary sql column  bin              VARBINARY MAX              AS VARCHAR MAX     FROM       SELECT CAST  EncodeOut AS VARCHAR MAX   AS bin   AS bin sql server temp   PRINT  DecodeOut

User · Answer

I did a script to convert an existing hash encoded in base64 to decimal  it may be useful   SELECT LOWER SUBSTRING CONVERT NVARCHAR 42   CAST   COLUMN NAME  as XML   value      varbinary max     1   3  40   from TABLE

User · Answer

Here is the code for the functions that will do the work  -- To Base64 string CREATE FUNCTION  dbo   fn str TO BASE64         STRING NVARCHAR MAX    RETURNS NVARCHAR MAX  AS BEGIN     RETURN           SELECT             CAST N   AS XML  value                     xs base64Binary xs hexBinary sql column  bin                         NVARCHAR MAX                   Base64Encoding         FROM               SELECT CAST  STRING AS VARBINARY MAX   AS bin           AS bin sql server temp       END GO  -- From Base64 string CREATE FUNCTION  dbo   fn str FROM BASE64         BASE64 STRING NVARCHAR MAX    RETURNS NVARCHAR MAX  AS BEGIN     RETURN           SELECT              CAST                  CAST N   AS XML  value  xs base64Binary sql variable   BASE64 STRING       VARBINARY MAX                 AS NVARCHAR MAX                  UTF8Encoding       END   Example of usage   DECLARE  CHAR NVARCHAR 256    N e g              or           SELECT  dbo   fn str FROM BASE64   dbo   fn str TO BASE64   CHAR   as converted

User · Answer

I know this has already been answered  but I just spent more time than I care to admit coming up with single-line SQL statements to accomplish this  so I ll share them here in case anyone else needs to do the same   -- Encode the string  TestData  in Base64 to get  VGVzdERhdGE   SELECT     CAST N   AS XML  value             xs base64Binary xs hexBinary sql column  bin                 VARCHAR MAX           Base64Encoding FROM       SELECT CAST  TestData  AS VARBINARY MAX   AS bin   AS bin sql server temp   -- Decode the Base64-encoded string  VGVzdERhdGE   to get back  TestData  SELECT      CAST          CAST N   AS XML  value               xs base64Binary  VGVzdERhdGE                  VARBINARY MAX                      AS VARCHAR MAX          ASCIIEncoding     I had to use a subquery-generated table in the first  encoding  query because I couldn t find any way to convert the original value   TestData   to its hex string representation   5465737444617461   to include as the argument to xs hexBinary   in the XQuery statement   I hope this helps someone

User · Answer

No  there is no native function  this method has worked for me in the past  http   www motobit com help scptutl sa306 htm so has this method  http   www vbforums com showthread php t 554886

[sql-server-2005] Base64 encoding in SQL Server 2005 T-SQL

Examples related to sql-server-2005

Examples related to tsql

Examples related to encoding

Examples related to base64