问题描述
正在寻找一个无错误测试的 sql 脚本,我可以在 UDF 中使用它来通过 sql 对 url 进行编码.函数将接收一个 URL 并传递一个 URL 编码的 URL.我看过一些,但我所遇到的似乎都有一些缺陷.
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.
推荐答案
为了使用此脚本,您需要使用 Numbers 表格.
In order to use this script, you'll want to use Numbers table.
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
该脚本在 SQL Server Central 上完全可用(需要注册)
The script is fully available on SQL Server Central (registration required)