Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

URLEncode Expand / Collapse
Author
Message
Posted Monday, March 31, 2008 4:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
Comments posted to this topic are about the item URLEncode

SQL guy and Houston Magician
Post #477291
Posted Tuesday, April 15, 2008 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 20, 2009 3:23 AM
Points: 1, 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 work;)

Good job at all
Post #485046
Posted Monday, July 20, 2009 11:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 7, 2010 7:23 AM
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
Post #756325
Posted Wednesday, March 31, 2010 4:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 7, 2012 2:54 PM
Points: 1, 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
Post #894273
Posted Thursday, December 19, 2013 5:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 10:28 AM
Points: 4, 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.
Post #1524839
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse