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?

回答1:

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!

回答2:

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 )

回答3:

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

回答4:

Here is the code for the functions that will do the work

-- To Base64 string
CREATE FUNCTION [dbo].[fn_str_TO_BASE64]
(
    @STRING VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT
            CAST(N\'\' AS XML).value(
                  \'xs:base64Binary(xs:hexBinary(sql:column(\"bin\")))\'
                , \'VARCHAR(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 VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT 
            CAST(
                CAST(N\'\' AS XML).value(\'xs:base64Binary(sql:variable(\"@BASE64_STRING\"))\', \'VARBINARY(MAX)\') 
            AS VARCHAR(MAX)
            )   UTF8Encoding
    )
END

回答5:

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

回答6:

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]

回答7:

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

回答8:

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