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 09, 2007 3:12 PM by Peter W. DeBetta
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

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

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks 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 (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement