SQL TSQL - 如何 URL 编码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2905193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL - How to URL Encode
提问by Billy Logan
Looking for a bug free tested sql script that i could use in a UDF to encode a url through sql. Function would take in a URL and pass out a URL Encoded URL. I have seen a few, but all i have come across seem to have some flaws.
寻找一个无错误测试的 sql 脚本，我可以在 UDF 中使用它来通过 sql 对 url 进行编码。函数将接收一个 URL 并传递一个 URL 编码的 URL。我看过一些，但我遇到的所有似乎都有一些缺陷。
采纳答案by Billy Logan
In order to use this script, you'll want to use Numbers table.
为了使用此脚本，您需要使用 Numbers 表。
CREATE FUNCTION [dbo].[URLEncode]
(@decodedString VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
/******
* select dbo.URLEncode('K8%/fwO3L mEQ*.}')
**/
DECLARE @encodedString VARCHAR(4000)
IF @decodedString LIKE '%[^a-zA-Z0-9*-.!_]%' ESCAPE '!'
BEGIN
SELECT @encodedString = REPLACE(
COALESCE(@encodedString, @decodedString),
SUBSTRING(@decodedString,num,1),
'%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString,num,1)))),3,3))
FROM dbo.numbers
WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString,num,1) like '[^a-zA-Z0-9*-.!_]' ESCAPE '!'
END
ELSE
BEGIN
SELECT @encodedString = @decodedString
END
RETURN @encodedString
END
GO
The script is fully available on SQL Server Central(registration required)
该脚本在SQL Server Central上完全可用（需要注册）
回答by Abe Miessler
How about this one by Peter DeBetta:
Peter DeBetta 的这个怎么样：
CREATE FUNCTION dbo.UrlEncode(@url NVARCHAR(1024))
RETURNS NVARCHAR(3072)
AS
BEGIN
DECLARE @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072)
SET @count = LEN(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i <= @count)
BEGIN
SET @c = SUBSTRING(@url, @i, 1)
IF @c LIKE N'[A-Za-z0-9()''*\-._!~]' COLLATE Latin1_General_BIN ESCAPE N'\' COLLATE Latin1_General_BIN
BEGIN
SET @urlReturn = @urlReturn + @c
END
ELSE
BEGIN
SET @urlReturn =
@urlReturn + '%'
+ SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),3,2)
+ ISNULL(NULLIF(SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),5,2), '00'), '')
END
SET @i = @i +1
END
RETURN @urlReturn
END
回答by FinnNk
Personally I would do this in the application rather than the DB - but if you have to for some reason and you can enable CLR Integration then this would be a perfect candidate for a CLR UDF. It'd be simpler than trying to do it in SQL and probably more reliable and performant too.
就我个人而言，我会在应用程序而不是数据库中执行此操作 - 但如果您出于某种原因必须这样做并且您可以启用 CLR 集成，那么这将是CLR UDF的完美候选者。它比尝试在 SQL 中执行它更简单，并且可能也更可靠和性能更高。
There are INET_URIEncodeand INET_URIDecodefunctions in the free version of the SQLsharp T-SQL CLR extension library. It handles Unicode too, though you need the paid version to handle non-standard %uXXYYencoding.
SQLsharp T-SQL CLR 扩展库的免费版本中有INET_URIEncode和INET_URIDecode函数。它也处理 Unicode，但您需要付费版本来处理非标准编码。%uXXYY
回答by Felix Eve
The question specifically asks for a function however here is a solution to url encode if your not able to create any functions:
这个问题特别要求一个函数，但是如果您无法创建任何函数，这里是一个 url 编码的解决方案：
select replace27
from TableName
cross apply (select replace1 = replace(T.TagText, '%', '%25')) r1
cross apply (select replace2 = replace(replace1, '&', '%26')) r2
cross apply (select replace3 = replace(replace2, '$', '%24')) r3
cross apply (select replace4 = replace(replace3, '+', '%2B')) r4
cross apply (select replace5 = replace(replace4, ',', '%2C')) r5
cross apply (select replace6 = replace(replace5, ':', '%3A')) r6
cross apply (select replace7 = replace(replace6, ';', '%3B')) r7
cross apply (select replace8 = replace(replace7, '=', '%3D')) r8
cross apply (select replace9 = replace(replace8, '?', '%3F')) r9
cross apply (select replace10 = replace(replace9, '@', '%40')) r10
cross apply (select replace11 = replace(replace10, '#', '%23')) r11
cross apply (select replace12 = replace(replace11, '<', '%3C')) r12
cross apply (select replace13 = replace(replace12, '>', '%3E')) r13
cross apply (select replace14 = replace(replace13, '[', '%5B')) r14
cross apply (select replace15 = replace(replace14, ']', '%5D')) r15
cross apply (select replace16 = replace(replace15, '{', '%7B')) r16
cross apply (select replace17 = replace(replace16, '}', '%7D')) r17
cross apply (select replace18 = replace(replace17, '|', '%7C')) r18
cross apply (select replace19 = replace(replace18, '^', '%5E')) r19
cross apply (select replace20 = replace(replace19, ' ', '%20')) r20
cross apply (select replace21 = replace(replace20, '~', '%7E')) r21
cross apply (select replace22 = replace(replace21, '`', '%60')) r22
cross apply (select replace23 = replace(replace22, '*', '%2A')) r23
cross apply (select replace24 = replace(replace23, '(', '%28')) r24
cross apply (select replace25 = replace(replace24, ')', '%29')) r25
cross apply (select replace26 = replace(replace25, '/', '%2F')) r26
cross apply (select replace27 = replace(replace26, '\', '%5C')) r27
The limitation of this solution it that is does not replace ASCII Control characters or Non-ASCII characters.
此解决方案的局限性在于不能替换 ASCII 控制字符或非 ASCII 字符。
Note it's important that the first replacement is for % so we don't escape any escape codes.
请注意，第一个替换是 % 很重要，因此我们不会转义任何转义码。
回答by Tony
Daniel Hutmacher from SQL Sunday has provided a nice function.
https://sqlsunday.com/2013/04/07/url-encoding-function/
SQL Sunday 的 Daniel Hutmacher 提供了一个很好的函数。
https://sqlsunday.com/2013/04/07/url-encoding-function/
CREATE FUNCTION dbo.fn_char2hex(@char char(1))
RETURNS char(2)
AS BEGIN
DECLARE @hex char(2), @dec int;
SET @dec=ASCII(@char);
SET @hex= --- First hex digit:
SUBSTRING('0123456789ABCDEF', 1+(@dec-@dec%16)/16, 1)+
--- Second hex digit:
SUBSTRING('0123456789ABCDEF', 1+( @dec%16) , 1);
RETURN(@hex);
END
CREATE FUNCTION dbo.fn_UrlEncode(@string varchar(max))
RETURNS varchar(max)
AS BEGIN
DECLARE @offset int, @char char(1);
SET @string = REPLACE(@string, '%', '%' + dbo.fn_Char2Hex('%'));
SET @offset=PATINDEX('%[^A-Z0-9.\-\%]%', @string);
WHILE (@offset!=0) BEGIN;
SET @char = SUBSTRING(@string, @offset, 1);
SET @string = REPLACE(@string, @char, '%' + dbo.fn_Char2hHx(@char));
SET @offset = PATINDEX('%[^A-Z0-9.\-\%]%', @string);
END
RETURN @string;
END;
