SQL Server中Base64編碼

淩亂°似流年 2022-07-13 10:37 277阅读 0赞

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

可以直接書寫為兩個標量值函數

Alter FUNCTION [dbo].[FromBase64] (         @encoded varchar(max) ) RETURNS varchar(max) AS BEGIN                 declare @decoded varbinary(max)         set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable(''@encoded''))', 'varbinary(max)')         RETURN convert(varchar(max), @decoded) END GO Alter FUNCTION [dbo].[ToBase64] (         @source varbinary(max) ) RETURNS varchar(max) AS BEGIN                 RETURN cast('' as xml).value('xs:base64Binary(sql:variable(''@source''))', 'varchar(max)') END GO

[https://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/][https_blog.falafel.com_t-sql-easy-base64-encoding-and-decoding]

[https://blogs.msdn.microsoft.com/sqltips/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa/][https_blogs.msdn.microsoft.com_sqltips_2008_06_30_converting-from-base64-to-varbinary-and-vice-versa]

[http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql][http_stackoverflow.com_questions_5082345_base64-encoding-in-sql-server-2005-t-sql]

[https_blog.falafel.com_t-sql-easy-base64-encoding-and-decoding]: https://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/
[https_blogs.msdn.microsoft.com_sqltips_2008_06_30_converting-from-base64-to-varbinary-and-vice-versa]: https://blogs.msdn.microsoft.com/sqltips/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa/
[http_stackoverflow.com_questions_5082345_base64-encoding-in-sql-server-2005-t-sql]: http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql