?
Solved

SQL URL-ENCODE finction

Posted on 2004-09-07
3
Medium Priority
?
4,177 Views
Last Modified: 2012-06-27
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
Comment
Question by:bukko
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Accepted Solution

by:
Otana earned 2000 total points
ID: 11995124
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

by:OlegP
ID: 11995196
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

by:bukko
ID: 11995625
Excellent - short and works great.

bukko
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question