Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

URLDecode or Hex to ASCI Char Expand / Collapse
Author
Message
Posted Thursday, April 29, 2004 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 16, 2006 11:33 AM
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




Post #113594
Posted Friday, April 30, 2004 8:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:09 PM
Points: 977, Visits: 277

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




Post #113883
Posted Friday, April 30, 2004 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 16, 2006 11:33 AM
Points: 2, Visits: 1

Thanks for that - it works a treat!

Regards

Glen




Post #113897
Posted Monday, January 16, 2006 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 16, 2006 10:21 AM
Points: 1, Visits: 1
Fantastic!
A 2 year old fn and still useful! Thanks guy.
Post #251023
Posted Friday, May 17, 2013 4:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:35 AM
Points: 1, Visits: 9
Thank You !
Great work, easy to use
Post #1453931
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse