Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


URLEncode


URLEncode

Author
Message
R Michael
R Michael
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 275
Comments posted to this topic are about the item URLEncode

SQL guy and Houston Magician
Blavette
Blavette
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 119
Hi,

This is a cool script, but it would be better if you had join more explanation about the table 'numbers'.

We have deduced that we must insert number from 1 to 256 in the table. without that, of course it doesn't workWink

Good job at all
alzowze-1025247
alzowze-1025247
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
Hello Robert Cary,

Thank you for the cool TSQL URLEncode script, it was almost exactly what I was looking for.

I slightly modified it to allieviate the need for creating/populating table numbers by selecting from a derived table using RowNum() as the incremented field [num]

Alzowze ";0)
ps... You wouldn't have a TSQL Is_UTF8 Function in your box of tricks would ya?

GO
SET ANSI_NULLS ON
GO

IF EXISTS (
SELECT 1
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[URLEncode]')
AND xtype IN (N'FN', N'IF', N'TF'))
BEGIN
DROP FUNCTION [dbo].[URLEncode]
END
GO

CREATE FUNCTION [dbo].[URLEncode]
(@decodedString VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
/*******************************************************************************************************
* dbo.URLEncode
* Source: http://www.sqlservercentral.com/scripts/URL/62679/
* Creator: Robert Cary
* Date: 03/18/2008
*
* Notes:
*
*
* Usage:
* select dbo.URLEncode('K8%/fwO3L mEQ*.}')
* select dbo.URLEncode('http://www.sqlservercentral.com/scripts/URL/62679/')
*
* Modifications:
* Developer Name Date Brief description
* ------------------- ----------- ------------------------------------------------------------
*
* Alzowze 21-Jul-09 Modifed to select from derived table.
* No need to create/populate table numbers.
* Used Union All, to ensure entire string gets encoded
* if LEN(@decodedString) = Maximum of 4000
********************************************************************************************************/

DECLARE @encodedString VARCHAR(4000)

IF @decodedString LIKE '%[^a-zA-Z0-9*-.!_]%' ESCAPE '!'
BEGIN
SELECT @encodedString = REPLACE(
COALESCE(@encodedString, @decodedString),
SUBSTRING(@decodedString,num,1),
'%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString,num,1)))),3,3))
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY number DESC) AS num
FROM master.dbo.spt_values
UNION All
SELECT ROW_NUMBER() OVER(ORDER BY number DESC) AS num
FROM master.dbo.spt_values
) AS num

WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString,num,1) like '[^a-zA-Z0-9*-.!_]' ESCAPE '!'


END
ELSE
BEGIN
SELECT @encodedString = @decodedString
END

RETURN @encodedString

END
GO
dinguot
dinguot
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
Modified function for languages using accented letters (french for example)

CREATE FUNCTION dbo.URLEncode(
@decodedString VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @encodedString VARCHAR(4000)

IF @decodedString LIKE '%[^azertyuiopmlkjhgfdsqwxcvbnAZERTYUIOPMLKJHGFDSQWXCVBN0-9*-.!_]%' ESCAPE '!'
BEGIN
SELECT @encodedString = REPLACE(
COALESCE(@encodedString, @decodedString),
SUBSTRING(@decodedString,num,1),
'%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString,num,1)))),3,3))
FROM dbo.numbers
WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString,num,1) like '[^azertyuiopmlkjhgfdsqwxcvbnAZERTYUIOPMLKJHGFDSQWXCVBN0-9*-.!_]' ESCAPE '!'
END
ELSE
BEGIN
SELECT @encodedString = @decodedString
END

RETURN @encodedString

END
Karl Dirck
Karl Dirck
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
Very Handy Function, thank you!
I found a bug though. It seems while it can URL encode UNENCODED URLs, if the URL already has an URL Encoded value, and you need to URL Encode the URL and it's already encoded value, you get erroneous and unexpected results.
To reproduce the issue:

DECLARE @base varchar(1024);
DECLARE @redirector varchar(1024);
DECLARE @t varchar(1024);
SET @redirector = 'http://redirector.reference.com/source=';
SET @base = 'http://www.sqlservercentral.com/scripts/URL/62679/';
SET @t = dbo.URLEncode(@base);
PRINT @t; -- http%3a%2f%2fwww.sqlservercentral.com%2fscripts%2fURL%2f62679%2f
SET @t = @redirector + dbo.URLEncode(@base);
PRINT @t; -- http://redirector.reference.com/source=http%3a%2f%2fwww.sqlservercentral.com%2fscripts%2fURL%2f62679%2f
SET @t = @redirector + dbo.URLEncode(@t);
PRINT @t; -- http://redirector.reference.com/source=http%252525252525253a%252525252525252f%252525252525252fredirector.reference.com%252525252525252fsource%252525252525253dhttp%252525252525253a%252525252525252f%252525252525252fwww.sqlservercentral.com%252525252525252fscripts%252525252525252fURL%252525252525252f62679%252525252525252f


Here is the expected value calculated using ASP.Net System.Web.HttpServerUtility.UrlEncode()

http%3a%2f%2fredirector.reference.com%2fsource%3dhttp%253a%252f%252fwww.sqlservercentral.com%252fscripts%252fURL%252f62679%252f


I researched the W3C recommendation and tested all valid non-alphanumeric characters in several platforms then encoded all characters that were encoded by any platform. Some encoded a space char (x20) as plus (+) as per the W3C, others as %20. Based testing and for consistency, I chose to encode all spaces as %20 for this implementation. Feel free to change it if your implementation requires.

Here is the complete solution that I am using:

-- Drop if already exists
IF EXISTS (
SELECT 1
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[URLEncode]')
AND xtype IN (N'FN', N'IF', N'TF'))
BEGIN
DROP FUNCTION [dbo].[URLEncode]
END

GO
CREATE FUNCTION [dbo].[URLEncode]
(@decodedString VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
/*******************************************************************************************************
* dbo.URLEncode
* Creator: Robert Cary
* Date: 03/18/2008
*
* Notes:
*
*
* Usage:
select dbo.URLEncode('K8%/fwO3L mEQ*.}')
* Modifications:
* Developer Name Date Brief description
* ------------------- ----------- ------------------------------------------------------------
* Karl Dirck 2013-12-12 Bug fix for encoding previously encoded values and encoded characters
*
********************************************************************************************************/

DECLARE @encodedString VARCHAR(4000)

IF @decodedString LIKE '%[^a-zA-Z0-9.!_-\%]%' ESCAPE '!'
BEGIN
--- Replace % with %25, that way, we can skip replacing the
--- % character once we're looping through the string.
SET @decodedString = REPLACE(@decodedString, '%', '%25');
SELECT @encodedString = REPLACE(
COALESCE(@encodedString, @decodedString),
SUBSTRING(@decodedString, num, 1),
'%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString, num, 1)))), 3, 3))
FROM dbo.numbers
WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString, num, 1) like '[^a-zA-Z0-9.!_-\%]' ESCAPE '!'
END
ELSE
BEGIN
SELECT @encodedString = @decodedString
END

RETURN @encodedString

END
GO


Corrected value:

http://redirector.reference.com/source=http%3a%2f%2fredirector.reference.com%2fsource%3dhttp%253a%252f%252fwww.sqlservercentral.com%252fscripts%252fURL%252f62679%252f



NOTE: The web site specified in the variable @redirector is fictional and used to simplify the case discovered to be an issue.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9902 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search