Treeless Software SQL example returns decoded URL line

Returns decoded URL line. For example, %20 is replaced with a space.

USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[ufs_URLDecode](@url varchar(3072)) 
RETURNS varchar(3072) 
AS 
BEGIN
    -- Peter DeBetta's SQL Programming Blog
    -- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072) 
    SET @count = LEN(@url) 
    SET @i = 1 
    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 + 2 
                END 
            ELSE 
                BEGIN
                    SET @urlReturn = @urlReturn + @c 
                END 
            SET @i = @i +1 
        END 
    RETURN @urlReturn 
END

Treeless Software

PC and Web Software Development.

Latest News

New VB.Net and SQL Examples

New Website Launched

July 7, 2011

Useful Links

Search