TSQL – Replace URL Escape Characters

This T-SQL code is to help parse out fields that need to be concatenated into a url string.
GO;
Create FUNCTION dbo.ReplaceURLEscapeCharacters
(@Token NVARCHAR(MAX))
/*
Created to parse Tokens that are being concatenated for URL generation.
Myles Yamada mylesyamada@me.com
*/
RETURNS NVARCHAR(MAX)
AS
BEGIN
 SELECT @Token = REPLACE( @Token, char(37), '%25')--% must be first because output contains this character
 SELECT @Token = REPLACE( @Token, char(32), '%20')--space(1)
 SELECT @Token = REPLACE( @Token, char(33), '%21')--!
 SELECT @Token = REPLACE( @Token, char(34), '%22')--"
 SELECT @Token = REPLACE( @Token, char(35), '%23')--#
 SELECT @Token = REPLACE( @Token, char(36), '%24')--$
 SELECT @Token = REPLACE( @Token, char(38), '%26')--^
 SELECT @Token = REPLACE( @Token, char(39), '%27')--'
 SELECT @Token = REPLACE( @Token, char(40), '%28')--(
 SELECT @Token = REPLACE( @Token, char(41), '%29')--)
 SELECT @Token = REPLACE( @Token, char(42), '%2A')--*
 SELECT @Token = REPLACE( @Token, char(43), '%2B')--+
 SELECT @Token = REPLACE( @Token, char(44), '%2C')--,
 SELECT @Token = REPLACE( @Token, char(45), '%2D')-- -
 SELECT @Token = REPLACE( @Token, char(46), '%2E')--.
 SELECT @Token = REPLACE( @Token, char(47), '%2F')--/
 SELECT @Token = REPLACE( @Token, char(58), '%3A')--:
 SELECT @Token = REPLACE( @Token, char(59), '%3B')--;
 SELECT @Token = REPLACE( @Token, char(60), '%3C')--<
 SELECT @Token = REPLACE( @Token, char(61), '%3D')--=
 SELECT @Token = REPLACE( @Token, char(62), '%3E')-->
 SELECT @Token = REPLACE( @Token, char(63), '%3F')--?
 SELECT @Token = REPLACE( @Token, char(64), '%40')--@
 SELECT @Token = REPLACE( @Token, char(91), '%5B')--[
 SELECT @Token = REPLACE( @Token, char(92), '%5C')--\
 SELECT @Token = REPLACE( @Token, char(93), '%5D')--]
 SELECT @Token = REPLACE( @Token, char(94), '%5E')--^
 SELECT @Token = REPLACE( @Token, char(95), '%5F')--_
 SELECT @Token = REPLACE( @Token, char(96), '%60')--`
 SELECT @Token = REPLACE( @Token, char(123), '%7B')--{
 SELECT @Token = REPLACE( @Token, char(124), '%7C')--|
 SELECT @Token = REPLACE( @Token, char(125), '%7D')--}
 SELECT @Token = REPLACE( @Token, char(126), '%7E')--~
RETURN @Token;
END
GO
--TEST
select dbo.ReplaceURLEscapeCharacters('#$^$@#%&(@#$%#%(*@')

Thanks to http://www.asciitable.com/ for the chart to build this script from.

This original work of art or code is provided free of charge for the betterment of humanity as a public service. No warranties or guarantees should be assumed or implied by the posting of this code. anyone using this code is doing so at their own risk. Be a good dba/developer and test anything found on the web in a test or dev box. 🙂