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
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
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
Source: Stackoverflow.com