透過 XQuery 進行 base64 編碼

參考以下網址:http://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)
set @source = convert(varbinary(max), ‘Hello Base64′)
set @encoded = cast(” as xml).value(‘xs:base64Binary(sql:variable("@source"))’, ‘varchar(max)’)
set @decoded = cast(” as xml).value(‘xs:base64Binary(sql:variable("@encoded"))’, ‘varbinary(max)’)

select
convert(varchar(max), @source) as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoded_binary,
convert(varchar(max), @decoded) as decoded_varchar

看來若要寫自訂函數,encode 可以將 binary 轉成 varchar,decode 將 varchar 轉成 binary

create function dbo.fnBase64Encode(@Binary varbinary(max))
returns varchar(max)
as
begin
    declare @encoded varchar(max)
    set @encoded=cast(" as xml).value(‘xs:base64Binary(sql:variable("@binary"))’, ‘varchar(max)’)
    return @encoded
end
go

create function dbo.fnBase64Decode(@Varchar varchar(max))
returns varbinary(max)
as
begin
    declare @decoded varbinary(max)
    set @decoded=cast(" as xml).value(‘xs:base64Binary(sql:variable("@varchar"))’, ‘varbinary(max)’)
    return @decoded
end
go

 

–驗證

declare @hash binary(20)=HASHBYTES(‘sha1′,’Password’)
select @hash ‘雜湊編碼後的二進位資料’,
datalength(dbo.fnBase64Encode(@hash)) ‘Base64 編碼後的長度’
select dbo.fnBase64Encode(@hash) ‘Base64 編碼後內容’,
dbo.fnBase64Decode(dbo.fnBase64Encode(@hash)) ‘Base64 解碼後原始資料’

結果如下:

image

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: