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