主页 / dba / 问题 / 230430
Accepted
Gabe
Asked: 2019-02-22 13:11:13 +0800 CST

解码内联表值函数中的 URL 编码字符？

  • 772

我正在寻找一个内联表值函数来解码 URL 编码字符（如 %20 用于空格）。参考

我看到了其他函数，但还没有看到一个内联表值函数。我很想在函数中编写一堆 REPLACE 语句，但我想知道是否有更好的方法。

CREATE FUNCTION dbo.itvfURLDecode
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN ( 
    SELECT REPLACE(REPLACE(@StringValue,'%2B','+'),'%20',' ') /* etc..*/ AS DecodedValue
)

;WITH cteData AS 
(
    SELECT 'This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25' AS example
)
SELECT 
    example, q.DecodedValue  /* returns: This+is a test<> ok? 100% */
FROM cteData c
CROSS APPLY dbo.itvfURLDecode(example) q
sql-server sql-server-2017

1 个回答

  1. Best Answer

    虽然我不确定您的特定需求的性能，但这里有一个使用TranslationTable CTE 和递归 CTE 的示例，它将查找并替换传入字符串中的值到 TVF。

    DROP FUNCTION IF EXISTS dbo.itvfURLDecode
go
CREATE FUNCTION dbo.itvfURLDecode
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT x.FindValue,x.ReplaceValue,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM (SELECT NULL AS a) a
    CROSS APPLY (
        --list of Find/Replace values
        VALUES ('%2B','+')
            ,('%20',' ')
            ,('%3C','<')
            ,('%3E','>')
            ,('%3F','?')
            ,('%25','%')
        ) x(FindValue, Replacevalue)
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

--Verify translation
SELECT *
FROM dbo.itvfURLDecode('This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

    | StrValue                  | cnt |
|---------------------------|-----|
| This+is a test<> ok? 100% | 0   |

    如果您注释掉--where cnt = 0TVF 的底部，您实际上可以看到递归 CTE 的进展

    | StrValue                                    | cnt |
|---------------------------------------------|-----|
| This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25 | 6   |
| This%2Bis%20a%20test%3C%3E%20ok%3F%20100%   | 5   |
| This%2Bis%20a%20test%3C%3E%20ok?%20100%     | 4   |
| This%2Bis%20a%20test%3C>%20ok?%20100%       | 3   |
| This%2Bis%20a%20test<>%20ok?%20100%         | 2   |
| This%2Bis a test<> ok? 100%                 | 1   |
| This+is a test<> ok? 100%                   | 0   |
    • 1

相关问题