sqlserver 中文base64编解码
1.前言。
如题，编解码格式都为gbk。编码可以不用函数。直接用for xml 。如：
SELECT (SELECT CONVERT(varbinary(max), '分为服务') FOR XML PATH(''), TYPE).value('/', 'nvarchar(max)')
2.解码。
CREATE FUNCTION dbo.base64_decode(@encoded_text varchar(max))RETURNS varbinary(max)ASBEGINDECLARE @output varbinary(max), @block_start int, @encoded_length int, @decoded_length int, @mapr binary(122)IF LEN(@encoded_text) & 3 > 0 OR @encoded_text LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=]%' COLLATE Latin1_General_Bin RETURN NULLSET @output = 0x-- The nth byte of @mapr contains the base64 value of the character with an ASCII value of n.-- eg. 65th byte = 0x00 = 0 = value of 'A'SET @mapr = 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF -- 1-33 + 0xFFFFFFFFFFFFFFFFFFFF3EFFFFFF3F3435363738393A3B3C3DFFFFFF00FFFFFF -- 33-64 + 0x000102030405060708090A0B0C0D0E0F10111213141516171819FFFFFFFFFFFF -- 65-96 + 0x1A1B1C1D1E1F202122232425262728292A2B2C2D2E2F30313233 -- 97-122--get the number of blocks to be decodedSET @encoded_length = LEN(@encoded_text)SET @decoded_length = @encoded_length / 4 * 3--for each blockSET @block_start = 1WHILE @block_start < @encoded_lengthBEGIN --decode the block and add to output --BINARY values between 1 and 4 bytes can be implicitly cast to INT SET @output = @output + CAST(CAST( SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start , 1)), 1) * 262144 + SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 1, 1)), 1) * 4096 + SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 2, 1)), 1) * 64 + SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 3, 1)), 1) AS int) AS binary(3)) SET @block_start = @block_start + 4ENDIF RIGHT(@encoded_text, 2) = '==' SET @decoded_length = @decoded_length - 2ELSE IF RIGHT(@encoded_text, 1) = '=' SET @decoded_length = @decoded_length - 1RETURN SUBSTRING(@output, 1, @decoded_length)END
