| Author |
 Topic  |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 09/05/2007 : 15:11:27
|
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
E 12°55'05.25"
N 56°04'39.16"
EDIT: Decoding + as Jeff mentioned |
Edited by - SwePeso on 09/05/2007 16:04:12
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/05/2007 : 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
USA
7423 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/05/2007 : 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
United Kingdom
22191 Posts |
Posted - 09/06/2007 : 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 |
Edited by - Kristen on 09/06/2007 10:01:14 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/06/2007 : 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')
ENDNow SELECT dbo.fnEncodeURL('Peso:Kristen') returns "Peso%3AKristen"
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/06/2007 : 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
USA
7423 Posts |
Posted - 09/06/2007 : 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
United Kingdom
22191 Posts |
Posted - 09/06/2007 : 10:27:08
|
Hahaha .. the boot's on the other foot!
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/07/2007 : 08:12:03
|
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
E 12°55'05.25"
N 56°04'39.16" |
|
|
| |
Topic |
|
|
|
URL decoding
