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