Decode URL encoded characters in inline table valued function?

Question

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

• McNets: Maybe a CLR function? Have a look at SQL#
• Mr.Brownstone: Preferably, this should be taken care of before it reaches the database. But, if you must do it, CLR is definitely the way to go.

Answer 1

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 |