SQLServer2005T-SQL 中的 Base64编码

我想编写一个 T-SQL 查询，其中将一个字符串编码为 Base64字符串。令人惊讶的是，我找不到任何用于执行 Base64编码的本地 T-SQL 函数。是否存在本机函数？如果没有，那么在 T-SQL 中执行 Base64编码的最佳方法是什么？

223777 次浏览
小开
最佳答案

我知道这个问题已经得到了回答，但是我只是花了更多的时间来完成这个问题，我不愿意承认我花了更多的时间来想出单行 SQL 语句来完成这个问题，所以我将在这里分享它们，以防其他人也需要这样做:

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

我不得不在第一个(编码)查询中使用一个子查询生成的表，因为我找不到任何方法将原始值(“ TestData”)转换为它的十六进制字符串表示形式(“546573744617461”) ，以便在 XQuery 语句中将参数包含为 xs: hexBinary ()。

我希望这对谁有帮助！

小开

下面是对 mercurial 答案的一个修改，它也在解码中使用子查询，允许在两个实例中使用变量。

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

这对于编码和解码非常有用。

小开

下面是完成这项工作的函数的代码

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

用法示例:

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

小开

对于 SQLServer2012及以上版本，我能找到的最简单、最短的方法是 BINARY BASE64:

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

为 Base64创建字符串

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

(或者用于 Unicode 字符串的 nvarchar(max))

小开

我喜欢@Slai 的回答。我只需要对我正在寻找的一行程序进行非常小的修改。我想我应该和大家分享一下我最终得到的东西，以防其他人也像我一样无意中发现了这个页面:

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

我做了一个脚本，将现有的 base64编码的散列转换为十进制，它可能会有用:

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

你可以只用:

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

然后在编码之后你会收到文本‘ MjE4Nqk5’

小开

从上面所有的答案中找出一些东西，下面是我得出的结论。

基本上有两种方法可以做到这一点:

;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

还有第二条路

;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

在比较性能时，第一个子树开销为2.4414，第二个子树开销为4.1538。这意味着第一个的速度是第二个的两倍(这是意料之中的，因为它使用 XML，这是出了名的慢)。

小开

以“斯莱”2015年的回答为基础，但以连续的方式写道:

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

提交答案