URLDecode or Hex to ASCI Char

  • 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

  • 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

  • Thanks for that - it works a treat!

    Regards

    Glen

  • Fantastic!

    A 2 year old fn and still useful! Thanks guy.

  • Thank You !

    Great work, easy to use 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply