SqlServer中将字符串转utf-8的函数、支持中文的UrlEncode函数
- 将字符串转utf-8的函数
SqlServer中字符串是ucs2格式(接近Unicode),如需要转为utf-8,需要进行转换:
create function fn_toUtf8Bin(@str nvarchar(max))
returns varbinary(max)
as begin
DECLARE @ucs2 varbinary(max), @r varbinary(max), @i int,@code INT
set @r=0x
SET @ucs2=Cast(@str AS VARBINARY(max))
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 @r=@r+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 @r=@r+Cast(@code/64+192 AS BINARY(1))
+ Cast(@code%64+128 AS BINARY(1))
ELSE
SET @r=@r+Cast(@code AS BINARY(1))
SET @i=@i + 2
end
return @r
end
- 支持中文的UrlEncode函数
create FUNCTION fn_UrlEncode(@str NVARCHAR(max))
RETURNS NVARCHAR(max)
AS BEGIN
-- 支持中文,同js的encodeURIComponent函数
DECLARE @ucs2 VARBINARY(max),
@cur VARCHAR(1),
@curbin VARBINARY(3)
SET @ucs2=Cast(@str AS VARBINARY(max))
DECLARE @output VARCHAR(max),
@i INT,
@code INT
SET @output=''
SET @i=1
WHILE 1 = 1
BEGIN
SET @cur=Cast(Substring(@ucs2, @i, 1) AS VARCHAR(1))
IF Substring(@ucs2, @i + 1, 1) = 0 AND @cur LIKE '[-A-Za-z0-9()''*._!~]'
BEGIN
SET @output=@output + @cur
END
ELSE
BEGIN
SET @code=Cast(Substring(@ucs2, @i+1, 1)+ Substring(@ucs2, @i, 1) AS INT)
IF @code = 0
BREAK
IF @code > 0x0800
SET @curbin=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 @curbin=Cast(@code/64+192 AS BINARY(1))
+ Cast(@code%64+128 AS BINARY(1))
ELSE
SET @curbin=Cast(@code AS BINARY(1))
DECLARE @re VARCHAR(8000),@ri INT
set @re = ''
set @ri = Datalength(@curbin)
WHILE @ri > 0
SELECT @re = '%'
+ Substring('0123456789ABCDEF', Substring(@curbin, @ri, 1)/16+1, 1)
+ Substring('0123456789ABCDEF', Substring(@curbin, @ri, 1)%16+1, 1)
+ @re
,@ri = @ri - 1
SET @output=@output + @re
END
SET @i=@i + 2
END
RETURN @output
END
前后端研究
前后端研究
推荐阅读
苹果IOS解析ISO8601格式的日期(结尾带不带Z)导致时区不对的问题
ISO8601格式下,结尾不带Z的日期,应为当前时区的时间,但苹果IOS下浏览器new Date()时解析有bug,把它解析为了UTC时间,导致在中国相差8小时。比如:2022-09-19T13:00:00 对应 Mon Sep 19 2022 13:00:00 GMT+080...
okfine阅读 445
关于 Python3 的编码(qbit)
Unicode 编号转字符 {代码...} 汉字与 gbk 十六进制/二进制之间的转换 {代码...} 数字与十六进制之间的转换 {代码...} 数字与八进制之间的转换 {代码...} 数字与二进制之间的转换 {代码...} bytes 相关 {代码...}...
qbit阅读 1.6k
java 中文繁简体转换工具 opencc4j 使用介绍 1.8.0
Opencc4jOpencc4j 支持中文繁简体转换,考虑到词组级别。在线体验Features 特点严格区分「一简对多繁」和「一简对多异」。完全兼容异体字,可以实现动态替换。严格审校一简对多繁词条,原则为「能分则不合」。词...
老马啸西风赞 2阅读 212
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。