URL decoding - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

URL decoding

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 15:11:27

[code]CREATE FUNCTION dbo.fnDeURL
(
@URL VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Position INT,
@Base CHAR(16),
@High TINYINT,
@Low TINYINT,
@Pattern CHAR(21)

SELECT @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@Position = PATINDEX(@Pattern, @URL)

WHILE @Position > 0
SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base),
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base),
@URL = STUFF(@URL, @Position, 3, CHAR(16 * @High + @Low - 17)),
@Position = PATINDEX(@Pattern, @URL)

RETURN REPLACE(@URL, '+', ' ')
END[/code]

E 12°55'05.25"
N 56°04'39.16"

EDIT: Decoding + as Jeff mentioned

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-05 : 15:59:29

Your subject says "URL Encoding" but the function does "URL Decoding".

Looks good, nice and short!

FYI -- URL Decoding should also decode + to a space.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-05 : 16:08:19

Perfect!

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 16:13:22

Thank you.

Now both + and %20 returns a single space.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 05:54:08

Here's my rather crappy EncodeURL function


CREATE FUNCTION dbo.fnEncodeURL
(
	@strInput	varchar(8000)	-- Note: String will get longer, so may overrun 8,000 characters
)
RETURNS varchar(8000)
/* WITH ENCRYPTION */
AS
/*
 * fnEncodeURL	Encode value to be used as URL (i.e. substitute reserved characters with %nn)
 *
 *	SELECT dbo.fnEncodeURL(MyColumnToURLEncode)
 *
 * Returns:
 *
 *	Encoded Content 
 *
 * HISTORY:
 *
 * 19-Jun-2004 KBM  Started
 */
BEGIN
	IF @strInput IS NULL
	BEGIN
		-- Just return NULL if input string IS NULL
		RETURN NULL
	END
	SELECT @strInput=
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(
					@strInput,
			    			'%', '%25'),	-- Must be inner-most replace
						CHAR(10), '%0A'),
						CHAR(13), '%0D'),
	      					' ', '%20'),
			    			':', '%3A'),
			    			';', '%3B'),
			    			'-', '%2D'),
			    			'/', '%2F'),
			    			'\', '%5C'),
			    			'!', '%21'),
			    			'"', '%22'),
			    			'#', '%23'),
			    			'?', '%3F'),
			    			'=', '%3D'),
			    			'@', '%40'),
--			    			'%', '%25'),
			    			'>', '%3E'),
			    			'<', '%3C'),
			    			'$', '%24'),
			    			'&', '%26'),
			    			'[', '%5B'),
			    			']', '%5D'),
			    			'~', '%7E'),
			    			'^', '%5E'),
			    			'`', '%60'),
			    			'{', '%7B'),
			    			'}', '%7D'),
			    			'|', '%7C')


	RETURN @strInput
END
/* TEST RIG


-- Code Example

	SELECT dbo.fnEncodeURL('8ECEE9BE-05BD-4DD2-9D09-6C121E0924E7')

 */
--================== fnEncodeURL ==================--
GO

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-06 : 07:17:39

I think you need % for the inner most REPLACE

SELECT dbo.fnEncodeURL('Peso:Kristen') returns "Peso%253AKristen"

ALTER FUNCTION dbo.fnEncodeURL
(
	@strInput	varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
	RETURN
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
			REPLACE(REPLACE(
					@strInput,
			    			'%', '%25'),
						CHAR(10), '%0A'),
						CHAR(13), '%0D'),
	      					' ', '%20'),
			    			':', '%3A'),
			    			';', '%3B'),
			    			'-', '%2D'),
			    			'/', '%2F'),
			    			'\', '%5C'),
			    			'!', '%21'),
			    			'"', '%22'),
			    			'#', '%23'),
			    			'?', '%3F'),
			    			'=', '%3D'),
			    			'@', '%40'),
			    			'>', '%3E'),
			    			'<', '%3C'),
			    			'$', '%24'),
			    			'&', '%26'),
			    			'[', '%5B'),
			    			']', '%5D'),
			    			'~', '%7E'),
			    			'^', '%5E'),
			    			'`', '%60'),
			    			'{', '%7B'),
			    			'}', '%7D'),
			    			'|', '%7C')
END

Now SELECT dbo.fnEncodeURL('Peso:Kristen') returns "Peso%3AKristen"

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 09:57:04

"I think you need % for the inner most REPLACE"

Yeah, you would be right.

I'm please to report that the QA Tester for that routine was shot at dawn!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-06 : 10:19:03

Hey Peso -- Just noticed that you did your REPLACE too late for the + to SPACE. %2B is the plus sign, but if you run:

select dbo.fnDeURL('jeff%2Bsmith')

instead of

jeff+smith

you get:

jeff smith

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 10:27:08

Hahaha .. the boot's on the other foot!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 08:12:03

[code]CREATE FUNCTION dbo.fnDeURL
(
@URL VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Position INT,
@Base CHAR(16),
@High TINYINT,
@Low TINYINT,
@Pattern CHAR(21)

SELECT @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@URL = REPLACE(@URL, '+', ' '),
@Position = PATINDEX(@Pattern, @URL)

WHILE @Position > 0
SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base),
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base),
@URL = STUFF(@URL, @Position, 3, CHAR(16 * @High + @Low - 17)),
@Position = PATINDEX(@Pattern, @URL)

RETURN @URL
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources