THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

T-SQL UrlDecode

While I'm at it, here is the counterpart to the UrlEncode function – UrlDecode:

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

Published Friday, March 9, 2007 3:12 PM by Peter W. DeBetta
Filed under:

Comments

 

abc said:

I need to find the source code on which the above mentioned link is generated

May 24, 2007 5:16 AM
 

Satish Vellanki said:

This is what I was looking for.

Just changed the parameter from varchar(3072) to nvarchar(max) and it went straight into our DB.

Thank you very much

August 14, 2007 4:11 PM
 

Josh said:

-- This will translate '+' to ' '

-- Awesome Function. You rock.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER FUNCTION [dbo].[UrlDecode](@url nvarchar(4000))

RETURNS nvarchar(4000)

AS

BEGIN

   DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn nvarchar(4000)

   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 IF @c = '+'

BEGIN

SET @c = ' '

SET @urlReturn = @urlReturn + @c

END

ELSE

BEGIN

SET @urlReturn = @urlReturn + @c

END

SET @i = @i +1

END

   RETURN @urlReturn

END

February 10, 2009 4:17 PM
 

Ashutosh said:

SUPERB Function.

Many thanks Peter!!!

April 21, 2009 8:18 PM
 

Try this now said:

You've given many helpful ideas here! I really shouldbe doing this my self. Cheers

July 12, 2011 7:26 PM
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement