SQL Server 2005 T-SQL 中的 Base64 编码 [英] Base64 encoding in SQL Server 2005 T-SQL
问题描述
我想编写一个 T-SQL 查询,其中我将一个字符串编码为 Base64 字符串.令人惊讶的是,我找不到任何用于进行 Base64 编码的本机 T-SQL 函数.是否存在本机功能?如果没有,在 T-SQL 中进行 Base64 编码的最佳方法是什么?
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?
推荐答案
我知道这已经得到了回答,但我只是花了更多的时间来想出单行 SQL 语句来完成这个,所以我将在此处分享,以防其他人需要这样做:
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
;
我不得不在第一个(编码)查询中使用子查询生成的表,因为我找不到任何方法将原始值(TestData")转换为其十六进制字符串表示(5465737444617461")以包含为XQuery 语句中 xs:hexBinary() 的参数.
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.
我希望这对某人有所帮助!
I hope this helps someone!
这篇关于SQL Server 2005 T-SQL 中的 Base64 编码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!