- 将字符串转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
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
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。