SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


URLDecode or Hex to ASCI Char


URLDecode or Hex to ASCI Char

Author
Message
Glen Conway
Glen Conway
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

Hi,

Does anyone have a URLDecode UDF or a UDF to convert HEX such as %20 to it's ASCII character?

Many thanks

Glen

----------

Author of Flarepath Windows Update Analyser - download yours today at http://www.flarepath.com/fwua





vadba
vadba
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 461

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





Glen Conway
Glen Conway
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

Thanks for that - it works a treat!

Regards

Glen





Michael Barron-294030
Michael Barron-294030
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Fantastic!
A 2 year old fn and still useful! Thanks guy.
Paul Godfrey
Paul Godfrey
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
Thank You !
Great work, easy to use :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search