Codificação Base64 no SQL Server 2005 T-SQL

Eu gostaria de escrever uma consulta T-SQL onde eu codifico uma string como uma string Base64. Surpreendentemente, não consigo encontrar nenhuma function T-SQL nativa para fazer codificação Base64. Existe uma function nativa? Se não, qual é a melhor maneira de fazer a codificação Base64 no T-SQL?

Eu sei que isso já foi respondido, mas eu passei mais tempo do que eu gostaria de admitir vindo com instruções SQL de linha única para conseguir isso, então eu vou compartilhá-las aqui, caso alguém mais precise fazer o mesmo:

-- 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 ; 

Eu tive que usar uma tabela gerada por subconsulta na primeira consulta (codificação) porque eu não consegui encontrar nenhuma maneira de converter o valor original (“TestData”) para sua representação de seqüência hexadecimal (“5465737444617461”) para include como o argumento para xs: hexBinary () na instrução XQuery.

Espero que isso ajude alguém!

A maneira mais simples e mais rápida que encontrei para o SQL Server 2012 e acima é BINARY BASE64 :

 SELECT CAST('string' as varbinary(max)) FOR XML PATH(''), BINARY BASE64 

Para Base64 para string

 SELECT CAST( CAST( 'c3RyaW5n' as XML ).value('.','varbinary(max)') AS varchar(max) ) 

(ou nvarchar(max) para cadeias de caracteres Unicode)

Aqui está uma modificação para a resposta do mercurial que também usa a subconsulta na decodificação, permitindo o uso de variables ​​em ambas as instâncias.

 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 

Aqui está o código para as funções que farão o trabalho

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

Não, não há function nativa, esse método funcionou para mim no passado: http://www.motobit.com/help/scptutl/sa306.htm
então tem esse método:
http://www.vbforums.com/showthread.php?t=554886

Eu amei a resposta do @Slai. Eu só tive que fazer modificações muito pequenas no one-liners que eu estava procurando. Eu pensei em dividir o que acabei fazendo, caso isso ajude alguém a entrar nessa página como eu fiz:

 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] 
 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] 

Isso é útil para codificar e decodificar.

De Bharat J

Eu fiz um script para converter um hash existente codificado em base64 para decimal, pode ser útil:

 SELECT LOWER(SUBSTRING(CONVERT(NVARCHAR(42), CAST( [COLUMN_NAME] as XML ).value('.','varbinary(max)'), 1), 3, 40)) from TABLE