url_unescape
URL escaped strings may be encountered when processing web logs or other web related data
This TSQL function demonstrates how a URL escaped string can be un-escaped using a series of nested calls to the 'replace' function. Each call to 'replace' looks for a single escaped character (like %32 or %25) and replaces it with the corresponding character.
To maximize performance this function processes only a limited set of 12 characters. When I wrote it I needed to process millions of lines from a web log. As is often the case when dealing with large volumes of data, performance is a greater concern then flexibility.
If your data contains other characters you can easily add them.
create schema [util]
grant select, exec on schema::[util] to public
create function [util].[url_unescape](
@data varchar(8000)
) returns varchar(8000)
as
/*
unit test this function
select util.url_unescape('%%25 &%26 (%28 )%29 ,%2c /%2f ;%3a =%3D ?%3F [%5B \%5C ]%5D')
Unescape the following characters
char, dec, hex
% 37 25
& 38 26
( 40 28
) 41 29
, 44 2C
/ 47 2F
; 58 3A
= 61 3D
? 63 3F
[ 91 5B
\ 92 5C
] 93 5D
*/
begin
return
replace( replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace( replace( @data, '%2F', '/')
,'%2C',','), '%29', ')'), '%28', '('), '%26', '&'), '%25', '%')
,'%3A',';'), '%3D', '='), '%3F', '?'), '%5B', '['), '%5C', '\'), '%5D', ']' )
end