March 31, 2008 at 4:19 pm
Comments posted to this topic are about the item URLEncode
SQL guy and Houston Magician
April 15, 2008 at 8:11 am
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
July 20, 2009 at 11:04 pm
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
* ------------------- ----------- ------------------------------------------------------------
*
*Alzowze21-Jul-09Modifed 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
March 31, 2010 at 4:08 pm
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
December 19, 2013 at 5:03 pm
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);
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:
NOTE: The web site specified in the variable @redirector is fictional and used to simplify the case discovered to be an issue.
May 12, 2016 at 6:51 am
Thanks for the script.
April 27, 2021 at 9:28 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply