Sql-server – Decode URL encoded characters in inline table valued function

sql serversql-server-2017

I'm looking for a inline table valued function that decodes URL encoded characters (like %20 for space). reference

I see other functions but haven't seen one that is an inline table valued function. I'm tempted in just writing a bunch of REPLACE statements in a function but I'm wondering if there's a better way.

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

Best Answer

While I'm not sure about the performance for you particular needs, here is an example of using a TranslationTable CTE along with a recursive CTE which will find and replace values in the incoming string to the 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   |

You can actually see the progression of the recursive CTE if you comment out the --where cnt = 0 towards the bottom of the TVF

| 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   |
Related Question