sql-server-2005t-sqlencodingbase64

Base64 encoding in SQL Server 2005 T-SQL


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?


Solution

  • 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.