[sql-server-2005] 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?

This question is related to sql-server-2005 tsql encoding base64

The answer is


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


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

enter image description here


You can use just:

Declare @pass2 binary(32)
Set @pass2 =0x4D006A00450034004E0071006B00350000000000000000000000000000000000
SELECT CONVERT(NVARCHAR(16), @pass2)

then after encoding you'll receive text 'MjE4Nqk5'


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

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]

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


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

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 )


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


Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to encoding

How to check encoding of a CSV file UnicodeEncodeError: 'ascii' codec can't encode character at special name Using Javascript's atob to decode base64 doesn't properly decode utf-8 strings What is the difference between utf8mb4 and utf8 charsets in MySQL? The character encoding of the plain text document was not declared - mootool script UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 23: ordinal not in range(128) How to encode text to base64 in python UTF-8 output from PowerShell Set Encoding of File to UTF8 With BOM in Sublime Text 3 Replace non-ASCII characters with a single space

Examples related to base64

How to convert an Image to base64 string in java? How to convert file to base64 in JavaScript? How to convert Base64 String to javascript file object like as from file input form? How can I encode a string to Base64 in Swift? ReadFile in Base64 Nodejs Base64: java.lang.IllegalArgumentException: Illegal character Converting file into Base64String and back again Convert base64 string to image How to encode text to base64 in python Convert base64 string to ArrayBuffer