在SQL Server 2005的T-SQL Base64编码 [英] Base64 encoding in SQL Server 2005 T-SQL

查看:327
本文介绍了在SQL Server 2005的T-SQL Base64编码的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想编写一个T-SQL查询,我带code字符串为Base64字符串。出人意料的是,我无法找到做Base64编码的任何本地T-SQL函数。难道一个本地函数存​​在吗?如果没有,什么是应该做Base64编码在T-SQL的最佳方式?

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)转换成它的十六进制字符串重新presentation(5465737444617461 )包括作为参数为xs:)在XQuery语句的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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆