Solved

SQL URL-ENCODE finction

Posted on 2004-09-07
3
4,012 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
3 Comments
 
LVL 11

Accepted Solution

by:
Otana earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can i use insert and update together by merging two tables? 9 30
SQL USE DATABASE VARIABLE 5 27
First Max value 3 28
Are triggers slow? 7 11
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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