UFN_DECODEURL
Return
| Return Type |
|---|
| nvarchar(4000) |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @URL | nvarchar(4000) | IN |
Definition
Copy
CREATE function [dbo].[UFN_DECODEURL] (@URL nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @POSITION int,
@BASE char(16),
@HIGH tinyint,
@LOW tinyint,
@PATTERN char(21),
@BYTE1VALUE int,
@SURROGATEHIGN int,
@SURROGATELOW int
select @PATTERN = '%[%][0-9a-f][0-9a-f]%',
@POSITION = Patindex(@PATTERN, @URL)
while @POSITION > 0
begin
select @HIGH = Ascii(Upper(Substring(@URL, @POSITION + 1, 1))) - 48,
@LOW = Ascii(Upper(Substring(@URL, @POSITION + 2, 1))) - 48,
@HIGH = @HIGH / 17 * 10 + @HIGH % 17,
@LOW = @LOW / 17 * 10 + @LOW % 17,
@BYTE1VALUE = 16 * @HIGH + @LOW
if @BYTE1VALUE < 128 --1-byte UTF-8
select @URL = Stuff(@URL, @POSITION, 3, Nchar(@BYTE1VALUE)),
@POSITION = Patindex(@PATTERN, @URL)
else if @BYTE1VALUE >= 192
and @BYTE1VALUE < 224
and @POSITION > 0 --2-byte UTF-8
begin
select @BYTE1VALUE = ( @BYTE1VALUE & ( Power(2, 5) - 1 ) ) *
Power(2
,
6),
@URL = Stuff(@URL, @POSITION, 3, ''),
@POSITION = Patindex(@PATTERN, @URL)
if @POSITION > 0
select @HIGH = Ascii(Upper(Substring(@URL, @POSITION + 1, 1)
))
- 48,
@LOW = Ascii(Upper(Substring(@URL, @POSITION + 2, 1))
)
- 48
,
@HIGH = @HIGH / 17 * 10 + @HIGH % 17,
@LOW = @LOW / 17 * 10 + @LOW % 17,
@BYTE1VALUE = @BYTE1VALUE + ( ( 16 * @HIGH + @LOW ) &
( Power(2, 6) - 1 )
),
@URL = Stuff(@URL, @POSITION, 3, Nchar(@BYTE1VALUE)),
@POSITION = Patindex(@PATTERN, @URL)
end
else if @BYTE1VALUE >= 224
and @BYTE1VALUE < 240
and @POSITION > 0 --3-byte UTF-8
begin
select @BYTE1VALUE = ( @BYTE1VALUE & ( Power(2, 4) - 1 ) ) *
Power(2
,
12),
@URL = Stuff(@URL, @POSITION, 3, ''),
@POSITION = Patindex(@PATTERN, @URL)
if @POSITION > 0
select @HIGH = Ascii(Upper(Substring(@URL, @POSITION + 1, 1)
))
- 48,
@LOW = Ascii(Upper(Substring(@URL, @POSITION + 2, 1))
)
- 48
,
@HIGH = @HIGH / 17 * 10 + @HIGH % 17,
@LOW = @LOW / 17 * 10 + @LOW % 17,
@BYTE1VALUE = @BYTE1VALUE + ( ( 16 * @HIGH + @LOW ) &
( Power(2, 6) - 1 )
) *
Power(2, 6),
@URL = Stuff(@URL, @POSITION, 3, ''),
@POSITION = Patindex(@PATTERN, @URL)
if @POSITION > 0
select @HIGH = Ascii(Upper(Substring(@URL, @POSITION + 1, 1)
))
- 48,
@LOW = Ascii(Upper(Substring(@URL, @POSITION + 2, 1))
)
- 48
,
@HIGH = @HIGH / 17 * 10 + @HIGH % 17,
@LOW = @LOW / 17 * 10 + @LOW % 17,
@BYTE1VALUE = @BYTE1VALUE + ( ( 16 * @HIGH + @LOW ) &
( Power(2, 6) - 1 )
),
@URL = Stuff(@URL, @POSITION, 3, Nchar(@BYTE1VALUE)),
@POSITION = Patindex(@PATTERN, @URL)
end
else if @BYTE1VALUE >= 240
and @POSITION > 0 --4-byte UTF-8
begin
select @BYTE1VALUE = ( @BYTE1VALUE & ( Power(2, 3) - 1 ) ) *
Power(2
,
18),
@URL = Stuff(@URL, @POSITION, 3, ''),
@POSITION = Patindex(@PATTERN, @URL)
if @POSITION > 0
select @HIGH = Ascii(Upper(Substring(@URL, @POSITION + 1, 1)
))
- 48,
@LOW = Ascii(Upper(Substring(@URL, @POSITION + 2, 1))
)
- 48
,
@HIGH = @HIGH / 17 * 10 + @HIGH % 17,
@LOW = @LOW / 17 * 10 + @LOW % 17,
@BYTE1VALUE = @BYTE1VALUE + ( ( 16 * @HIGH + @LOW ) &
( Power(2, 6) - 1 )
) *
Power(2, 12),
@URL = Stuff(@URL, @POSITION, 3, ''),
@POSITION = Patindex(@PATTERN, @URL)
if @POSITION > 0
select @HIGH = Ascii(Upper(Substring(@URL, @POSITION + 1, 1)
))
- 48,
@LOW = Ascii(Upper(Substring(@URL, @POSITION + 2, 1))
)
- 48
,
@HIGH = @HIGH / 17 * 10 + @HIGH % 17,
@LOW = @LOW / 17 * 10 + @LOW % 17,
@BYTE1VALUE = @BYTE1VALUE + ( ( 16 * @HIGH + @LOW ) &
( Power(2, 6) - 1 )
) *
Power(2, 6),
@URL = Stuff(@URL, @POSITION, 3, ''),
@POSITION = Patindex(@PATTERN, @URL)
if @POSITION > 0
begin
select @HIGH = Ascii(Upper(Substring(@URL, @POSITION + 1
,
1)))
- 48,
@LOW = Ascii(Upper(Substring(@URL, @POSITION + 2,
1
)))
- 48,
@HIGH = @HIGH / 17 * 10 + @HIGH % 17,
@LOW = @LOW / 17 * 10 + @LOW % 17,
@BYTE1VALUE = @BYTE1VALUE + ( ( 16 * @HIGH + @LOW
)
& (
Power(2, 6) - 1 )
)
select @SURROGATEHIGN = ( ( @BYTE1VALUE - Power(16, 4) )
&
(
Power(2, 20) - 1 ) ) /
Power(2, 10) + 13 *
Power(16, 3) +
8
*
Power(16, 2),
@SURROGATELOW = ( ( @BYTE1VALUE - Power(16, 4) )
&
( Power(2, 10) - 1 ) )
+ 13 *
Power(16, 3) +
12 * Power(16, 2),
@URL = Stuff(@URL, @POSITION, 3, Nchar(
@SURROGATEHIGN)
+ Nchar(
@SURROGATELOW)
),
@POSITION = Patindex(@PATTERN, @URL)
end
end
end
return Replace(@URL, '+', ' ')
end