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

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

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

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

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

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

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

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

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  quot bin quot        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  quot Base64Encoding quot       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

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

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

[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