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.