URLEncode

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Cary » URLEncode

URLEncode

Rate Topic

Display Mode

Topic Options

Author

Message

R Michael

Posted Monday, March 31, 2008 4:19 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, June 14, 2013 11:00 PM
Points: 263, Visits: 274

Comments posted to this topic are about the item URLEncode

SQL guy and Houston Magician

Post #477291

Blavette

Posted Tuesday, April 15, 2008 8:11 AM

Forum 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

alzowze-1025247

Posted Monday, July 20, 2009 11:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 07, 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

dinguot

Posted Wednesday, March 31, 2010 4:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 07, 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

« Prev Topic | Next Topic »

Permissions