Here's a possible solution using two UDF's, dbo.fnHex2Int and dbo.fnURLDecode:
-- Example:
-- SELECT dbo.fnURLDecode ('http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=113594')
-- SELECT dbo.fnURLDecode ('http://www.sql%20server%20central.com/forums/shwmessage.aspx?messageid=113594')
GO
CREATE FUNCTION dbo.fnHex2Int
(
@hexNum varchar(20)
)
RETURNS int
AS
BEGIN
DECLARE @hexdigits varchar(16), @next char(1), @num int, @multiplier int
DECLARE @cpos int, @cpos_max int
SET @cpos_max = Len(@hexNum)
-----------------------------------------------
-- largest int is 2147483647, or 0x7FFFFFFF
IF @cpos_max > 8
BEGIN
RETURN NULL
END
IF @cpos_max = 8 AND Left(@hexnum,1) > '7'
BEGIN
RETURN NULL
END
-----------------------------------------------
SET @hexdigits = '0123456789ABCDEF'
SET @multiplier = 1
SET @num = 0
SET @cpos = @cpos_max
WHILE @cpos > 0
BEGIN
SET @next = Substring(@hexnum, @cpos, 1)
SET @num = @num + (CharIndex(@next , @hexdigits, 1) - 1) * @multiplier
SET @cpos = @cpos - 1
IF @cpos > 0
SET @multiplier = @multiplier * 16
END
RETURN @num
END
------------------------------------------------------------------
GO
-- I based dbo.fnURLDecode on the VB function URLDecode
-- written by Markus Diersbock
-- Source code located at
-- http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.44365/lngWId.1/qx/vb/scripts/ShowCode.htm
CREATE FUNCTION dbo.fnURLDecode (@sEncodedURL varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @i int, @maxi int, @sRtn varchar(8000), @sTmp varchar(8000)
SET @sRtn = ''
IF Len(@sEncodedURL) > 0
BEGIN
-- Loop through each char
SET @i = 1
SET @maxi = Len(@sEncodedURL)
WHILE @i <= @maxi
BEGIN
SET @sTmp = SubString(@sEncodedURL, @i, 1)
SET @sTmp = Replace(@sTmp, '+', ' ')
-- If char is % then get next two chars
-- and convert from HEX to decimal
IF @sTmp = '%'
BEGIN
-- process following two-digit hex number - convert to decimal, then char
SET @sTmp = SubString(@sEncodedURL, @i + 1, 2)
SET @sTmp = Char( dbo.fnHex2Int(@sTmp) )
SET @i = @i + 2
END
SET @sRtn = @sRtn + @sTmp
SET @i = @i + 1
END
RETURN @sRtn
END
RETURN ''
END -- Function
GO