URL Decoding in SQL

by Joe Havelick 16. July 2009 14:19

CREATE FUNCTION dbo.UrlDecode(@url varchar(3072))
RETURNS varchar(3072)
AS
BEGIN 
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072
    SET @count = Len(@url
    SET @i =
    SET @urlReturn = '' 
    WHILE (@i <= @count
     BEGIN 
        SET @c = substring(@url, @i, 1
        IF @c LIKE '[!%]' ESCAPE '!' 
         BEGIN 
            SET @cenc = substring(@url, @i + 1, 2
            SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 1, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int
                            END * 16
                            CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 2, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int) 
                            END
            SET @urlReturn = @urlReturn + @c 
            SET @i = @i +
         END 
        ELSE 
         BEGIN 
            SET @urlReturn = @urlReturn + @c 
         END 
        SET @i = @i +
     END 
    RETURN @urlReturn
END
GO

References:

http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx

 

Tags:

Tech Tips

Comments are closed

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations