CREATE FUNCTION dbo.base64_decode ( @encoded_text varchar(max) ) RETURNS varbinary(max) AS BEGIN DECLARE @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 NULL SET @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 decoded SET @encoded_length = LEN(@encoded_text) SET @decoded_length = @encoded_length / 4 * 3 --for each block SET @block_start = 1 WHILE @block_start < @encoded_length BEGIN --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 + 4 END IF RIGHT(@encoded_text, 2) = '==' SET @decoded_length = @decoded_length - 2 ELSE IF RIGHT(@encoded_text, 1) = '=' SET @decoded_length = @decoded_length - 1 RETURN SUBSTRING(@output, 1, @decoded_length) ENDbase64解码的函数改成这样,返回解码后的字节流,更具一般性。 根据返回的字节流(假定是@varbins)的字符集再转成字符串: 如果是ucs-2,直接CAST(@varbins AS nvarchar(max)); 如果是gb2312(GBK),在数据库排序规则是"Chinese_PRC_CI_AS"的情况下CAST(@varbins AS varchar(max)); 如果是utf-8,只能自己写转换函数了。
终于写了一个转UTF-8的函数:CREATE FUNCTION dbo.utf8_to_ucs2( @utf8 varbinary(max) ) RETURNS varbinary(max) AS BEGIN DECLARE @output varbinary(max), @i int, @next int, @code int, @tmp varbinary(1) SET @output = 0x SET @i = 1 SET @next = 0 WHILE 1 = 1 BEGIN SET @tmp = SUBSTRING(@utf8,@i,1) IF @tmp = 0x BREAK IF @tmp BETWEEN 0x01 AND 0x7F SET @output = @output + @tmp + 0x00 ELSE IF @tmp BETWEEN 0xC0 AND 0xDF BEGIN SET @code = (CAST(@tmp AS int) & 0x1F) * 64 SET @next = 1 END ELSE IF @tmp BETWEEN 0xE0 AND 0xEF BEGIN SET @code = (CAST(@tmp AS int) & 0x0F) * 4096 SET @next = 2 END ELSE IF @tmp BETWEEN 0x80 AND 0xBF AND @next IN (1,2) BEGIN IF @next = 1 BEGIN SET @code = @code + (CAST(@tmp AS int) & 0x3F) SET @output = @output + CAST(NCHAR(@code) AS binary(2)) END IF @next = 2 SET @code = @code + (CAST(@tmp AS int) & 0x3F) * 64 SET @next = @next - 1 END ELSE RETURN NULL SET @i = @i + 1 END RETURN @output END 测试:SELECT CAST(dbo.utf8_to_ucs2(dbo.base64_decode('6K+277yM6Kej5LiN5Ye65p2l5ZWK77yM5ZKL5Yqe5Yqe57G7')) AS nvarchar(max))
附送一个UCS2转UTF8的函数:CREATE FUNCTION dbo.ucs2_to_utf8( @ucs2 varbinary(max) ) RETURNS varbinary(max) AS /* U-00000000 ... U-0000007F 0xxxxxxx U-00000080 ... U-000007FF 110xxxxx 10xxxxxx U-00000800 ... U-0000FFFF 1110xxxx 10xxxxxx 10xxxxxx */ BEGIN DECLARE @output varbinary(max), @i int, @code int SET @output = 0x SET @i = 1 WHILE 1 = 1 BEGIN SET @code = CAST(SUBSTRING(@ucs2,@i+1,1) + SUBSTRING(@ucs2,@i,1) AS int) IF @code = 0 BREAK IF @code >= 0x0800 SET @output = @output + CAST(@code / 4096 + 224 AS binary(1)) + CAST((@code % 4096) / 64 + 128 AS binary(1)) + CAST((@code % 4096) % 64 + 128 AS binary(1)) ELSE IF @code >= 0x0080 SET @output = @output + CAST(@code / 64 + 192 AS binary(1)) + CAST(@code % 64 + 128 AS binary(1)) ELSE SET @output = @output + CAST(@code AS binary(1)) SET @i = @i + 2 END RETURN @output END 不过用T-SQL做转码工作真不是什么好事,太麻烦了。用前端代码只要调一个函数就搞定了。
CREATE FUNCTION dbo.base64_decode
(
@encoded_text varchar(max)
)
RETURNS varbinary(max)
AS
BEGIN
DECLARE
@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 NULL
SET @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 decoded
SET @encoded_length = LEN(@encoded_text)
SET @decoded_length = @encoded_length / 4 * 3
--for each block
SET @block_start = 1
WHILE @block_start < @encoded_length
BEGIN
--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 + 4
END
IF RIGHT(@encoded_text, 2) = '=='
SET @decoded_length = @decoded_length - 2
ELSE IF RIGHT(@encoded_text, 1) = '='
SET @decoded_length = @decoded_length - 1
RETURN SUBSTRING(@output, 1, @decoded_length)
ENDbase64解码的函数改成这样,返回解码后的字节流,更具一般性。
根据返回的字节流(假定是@varbins)的字符集再转成字符串:
如果是ucs-2,直接CAST(@varbins AS nvarchar(max));
如果是gb2312(GBK),在数据库排序规则是"Chinese_PRC_CI_AS"的情况下CAST(@varbins AS varchar(max));
如果是utf-8,只能自己写转换函数了。
@utf8 varbinary(max)
)
RETURNS varbinary(max)
AS
BEGIN
DECLARE
@output varbinary(max),
@i int,
@next int,
@code int,
@tmp varbinary(1)
SET @output = 0x
SET @i = 1
SET @next = 0
WHILE 1 = 1
BEGIN
SET @tmp = SUBSTRING(@utf8,@i,1)
IF @tmp = 0x
BREAK
IF @tmp BETWEEN 0x01 AND 0x7F
SET @output = @output + @tmp + 0x00
ELSE IF @tmp BETWEEN 0xC0 AND 0xDF
BEGIN
SET @code = (CAST(@tmp AS int) & 0x1F) * 64
SET @next = 1
END
ELSE IF @tmp BETWEEN 0xE0 AND 0xEF
BEGIN
SET @code = (CAST(@tmp AS int) & 0x0F) * 4096
SET @next = 2
END
ELSE IF @tmp BETWEEN 0x80 AND 0xBF AND @next IN (1,2)
BEGIN
IF @next = 1
BEGIN
SET @code = @code + (CAST(@tmp AS int) & 0x3F)
SET @output = @output + CAST(NCHAR(@code) AS binary(2))
END
IF @next = 2
SET @code = @code + (CAST(@tmp AS int) & 0x3F) * 64
SET @next = @next - 1
END
ELSE
RETURN NULL
SET @i = @i + 1
END
RETURN @output
END
测试:SELECT CAST(dbo.utf8_to_ucs2(dbo.base64_decode('6K+277yM6Kej5LiN5Ye65p2l5ZWK77yM5ZKL5Yqe5Yqe57G7')) AS nvarchar(max))
@ucs2 varbinary(max)
)
RETURNS varbinary(max)
AS
/*
U-00000000 ... U-0000007F 0xxxxxxx
U-00000080 ... U-000007FF 110xxxxx 10xxxxxx
U-00000800 ... U-0000FFFF 1110xxxx 10xxxxxx 10xxxxxx
*/
BEGIN
DECLARE
@output varbinary(max),
@i int,
@code int
SET @output = 0x
SET @i = 1
WHILE 1 = 1
BEGIN
SET @code = CAST(SUBSTRING(@ucs2,@i+1,1) + SUBSTRING(@ucs2,@i,1) AS int)
IF @code = 0
BREAK
IF @code >= 0x0800
SET @output = @output +
CAST(@code / 4096 + 224 AS binary(1)) +
CAST((@code % 4096) / 64 + 128 AS binary(1)) +
CAST((@code % 4096) % 64 + 128 AS binary(1))
ELSE IF @code >= 0x0080
SET @output = @output +
CAST(@code / 64 + 192 AS binary(1)) +
CAST(@code % 64 + 128 AS binary(1))
ELSE
SET @output = @output + CAST(@code AS binary(1))
SET @i = @i + 2
END
RETURN @output
END
不过用T-SQL做转码工作真不是什么好事,太麻烦了。用前端代码只要调一个函数就搞定了。
服务器: 消息 257,级别 16,状态 3,行 1
不允许从数据类型 varchar 到 varbinary 的隐性转换。请使用 CONVERT 函数来运行此查询。