Solved

# SQL URL-ENCODE finction

Posted on 2004-09-07
3,898 Views
Hi,

I need a URL-ENCODE function in SQL which will return a URL safe version of the value passed, e.g.:

Mickey Mouse becomes Mickey%20Mouse

I would take me a while to get this absolutely right and I don't have the time, so if someone has one I'll swap it for 500pts and an A grade.

Regards

bukko
0
Question by:bukko

LVL 11

Accepted Solution

Otana earned 500 total points
Try this one:

CREATE FUNCTION udfURLencode (@String nvarchar(4000))
RETURNS nvarchar(4000) AS
BEGIN

DECLARE @ReturnString nvarchar(4000)

SET @ReturnString = replace(replace(replace(replace(replace(replace(replace(@String,'%','%25'),'+','%20'),' ','%20'),'/','%2F'),'?','%25'),'#','%23'),'&','%26')

RETURN @ReturnString

END
0

LVL 6

Expert Comment

CREATE FUNCTION DBO.URL_ENCODE (@A VARCHAR(8000))
RETURNS VARCHAR(8000)  AS
BEGIN
DECLARE @I AS INT
DECLARE @F AS INT

SET @A=LTRIM(RTRIM(@A))
SET @I=1
SET @F=0
IF LEN(@A)>1
WHILE @I<>LEN(@A)
BEGIN
IF SUBSTRING(@A,@I,1)=' '
IF @F=0
BEGIN
SET @A=STUFF (@A,@I,1,'%20')
SET @I=@I+2
SET @F=1
END
ELSE
IF @F=1
SET @F=0
SET @I=@I+1
END
SET @A=REPLACE(@A,' ','')
RETURN @A
END
0

LVL 8

Author Comment

Excellent - short and works great.

bukko
0

## Featured Post

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.