InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 15:10:43 GMT20http://www.sqlservercentral.com/Forums/Topic477291-329-1.aspxModified function for languages using accented letters (french for example)CREATE FUNCTION dbo.URLEncode( @decodedString VARCHAR(4000))RETURNS VARCHAR(4000)ASBEGINDECLARE @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 '!'ENDELSEBEGIN SELECT @encodedString = @decodedStringENDRETURN @encodedStringENDWed, 31 Mar 2010 16:08:45 GMTdinguothttp://www.sqlservercentral.com/Forums/Topic477291-329-1.aspxHello 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?GOSET ANSI_NULLS ONGOIF 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]ENDGOCREATE FUNCTION [dbo].[URLEncode] (@decodedString VARCHAR(4000))RETURNS VARCHAR(4000)ASBEGIN/******************************************************************************************************** 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 '!'ENDELSEBEGIN SELECT @encodedString = @decodedString ENDRETURN @encodedStringENDGOMon, 20 Jul 2009 23:04:30 GMTalzowze-1025247http://www.sqlservercentral.com/Forums/Topic477291-329-1.aspxHi,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 allTue, 15 Apr 2008 08:11:09 GMTBlavettehttp://www.sqlservercentral.com/Forums/Topic477291-329-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/URL/62679/">URLEncode</A>[/B]Mon, 31 Mar 2008 16:19:26 GMTRobert Michael Cary