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?

Hypocrisy

I'd question why data should be stored as a base64 string. There's a good reason to use base64 over http, namely that it ensures interoperability across systems that support nothing more than the ASCII character set (and that treat all of the data as text). You can easily convert a byte array to base-64 and vice versa, so why not store the data efficiently? I've even seen people store base64-strings in nvarchar columns, which takes 275% the space of varbinary, leading to waste of disk, ram, network, etc. – Jentoft

This is about generating a base64 string, not storing one. – Hypocrisy

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

Starveling

When encoding, xs:base64Binary(sql:column("bin")) (without the xs:hexBinary call) works as well. Great help! – Babel

To support encoding of unicode text, you should add 'N' in front of TestData: 'SELECT CAST(N'TestData' AS VARBINARY(MAX)) AS bin' – Smacking

Didn't work for unicode text... SELECT CAST(N'' AS XML).value( 'xs:base64Binary(xs:hexBinary(sql:column("bin")))' , 'VARCHAR(MAX)' ) Base64Encoding FROM ( SELECT CAST(N'मन्त्रीले उल्ट्याए सात छन्।' AS VARBINARY(MAX)) AS bin ) AS bin_sql_server_temp; – Sd

#18872179 – Sd

@Sd varchar isn't compatable with Unicode. It works fine if you use nvarchar(max) instead, e.g.: SELECT CAST( CAST(N'' AS XML).value( 'xs:base64Binary("LgkoCU0JJAlNCTAJQAkyCUcJIAAJCTIJTQkfCU0JLwk+CQ8JIAA4CT4JJAkgABsJKAlNCWQJ")' , 'VARBINARY(MAX)' ) AS NVARCHAR(MAX) ) UnicodeEncoding ; – Nalchik

I think the solution has a strong smell of "I want to do it in SQL, whether that's smart or not"! Since it seems the data is stored as varbinary, why not fetch the bytes and convert them at the receiving end? Not only will the DB and DAL code be totally straightforward - Convert.ToBase64(data) will do it - but you'll avoid a ton of needless data transfer as well. – Jentoft

Because sometimes people need to accomplish certain things in software for reasons you can't always predict ... ? – Starveling

like for me I am REQUIRED to make an sql script that does this exact thing (it's not working for me though since I still get an Invalid length for a Base-64 char array or string exception)... – Umeh

It works perfect even with unicode, adding N encodes only the first character. – Beyer

I have incoming Base64Encoded strings (not from SQL Server) and to be able to decode them successfully, one must Carefully ensure that the strings were UTF-16LE (Little Endian - SQL Servers encoding for Nvarchar), Before the initial Base64 encoding. Otherwise it will not work for Unicode strings! – Rhyner

To view the decode the text message fully in sql server, select the "Result to Text" option in sql server . – Amero

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

Chromous

This is much simpler than the other answers, and works just as well – Peeler

This was much simpler for me as well and exactly what i needed. I only wrapped the FOR XML PATH query as a sub-query: DECLARE @Encoded VARCHAR(500) = CONVERT(VARCHAR(500), (SELECT CONVERT(VARBINARY, @Source) FOR XML PATH(''), BINARY BASE64)) – Thalia

what is the purpose of BINARY BASE64 in the first line? Is it needed? I tried without and it seems to give the same result. – Heerlen

@Heerlen seems it is needed in some cases like BLOB objects msdn.microsoft.com/en-us/library/bb522494.aspx – Chromous

@Hraefn Thanks, I didn't know that varbinary(max) is needed for more than 8000 bytes – Chromous

This should be the answer because the actual answer requires string literals and cannot accept variables like this answer can. – Barracks

I like the simplicity of this solution, but I can't figure out how to use it against a table with multiple rows without them being combined into a single line. – Lineate

@tubelius - A different approach would be simpler, assuming the source column is already a varbinary. E.g. SELECT CONVERT(xml, N'').value('xs:base64Binary(sql:column("t.SomeColumn"))', 'varchar(max)') FROM SomeTable AS t; . FOR XML collapses the resultset to a single variable, so you have to do extra work to expand it back out. – Respire

For base64 to string, I'm noticing a significant perf gain with .value('data[1]','varbinary(max)') vice .value('.','varbinary(max)'). – Calise

This appears to be working well on SQL Server 2008. – Rufford

Thanks, I was using the Cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'NVARCHAR(max)') in a stored function before, but for some unexplained reason it was crashing and killing the session in SQL 2019 with compatibility level 150 - it worked inline, so no idea of the cause. This is cleaner, and works for all version. – Gunfight

Answer 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

Purlieu

Answer 4

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

Figural

Generally useful. This didn't handle any characters like persian and russian, or emoji. e.g., سلام جیران or В России Base64 кодирует вас or ❤️💥🤪🦌🎅⛄🎄🤐🙈🙉🙊💩 – Cutback

You're right. It handles after replacing varchar to nvarchar – Figural

Answer 5

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]

Thalia

For me, I needed to change the second line VARBINARY to VARBINARY(56), and then it worked. – Abingdon

Shortest solution, SQL Server 2005+ compatible. – Ryder

Very nice. I used this when I was generating random test data. I used it to turn unprintable characters from crypt_gen_random(N) into something more readable: SELECT CONVERT(VARCHAR(MAX), (SELECT CRYPT_GEN_RANDOM(20) FOR XML PATH(''), BINARY BASE64)). – Homonym

Answer 6

sp_helptext usf_base64_encode
create FUNCTION [dbo].[usf_base64_encode]  
(  
    @value varchar(max)  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    DECLARE @source varbinary(max) = convert(varbinary(max), @value)  
    RETURN cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')  
END

Cineaste

This is a build-in function used in SQL Server 2016 – Cineaste

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Odyl

Answer 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 useful for encode and decode.

Odetteodeum

Answer 8

Building on "Slai"s answer from 2015 but written in a sequential way:

declare @s as varchar(max) 
declare @b64 as varchar(max) 

-- encode varchar to base64 varchar
set @s  = 'Hello Base64'
set @b64  = (select CAST(@s as varbinary(max)) FOR XML PATH(''), BINARY BASE64)
select @s source, @b64 b86Encoded

-- decode base64 varchar 
set @b64 = 'SGVsbG8gQmFzZTY0'
set @s = CAST( CAST( @b64 as XML ).value('.','varbinary(max)') AS varchar(max) )
select @b64 b64Encoded, @s decoded

Mundane

Answer 9

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

Gastongastralgia

Answer 10

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

Kallick

Answer 11

You can use just:

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

then after encoding you'll receive text 'MjE4Nqk5'

Castorina