I was perusing the newsgroups when I came across a request in the microsoft.public.sqlserver.clr newsgroup to see if there was a way to use System.Web.HttpUtility.UrlEncode in T-SQL. I know using the CLR is an option, but I decided to write a T-SQL equivalent. I reflected the code to figure out the exact rules for UrlEncoding. Then, I discovered much to my delight, a new system function in SQL Server 2005 that converts a string to its hexadecimal equivalent as a string– sys.fn_varbintohexstr. I knew there was an extended stored procedure in prior versions of SQL Server to do this conversion, but this function made writing this UrlEncode function even easier.
CREATE FUNCTION dbo.UrlEncode(@url varchar(1024))
RETURNS varchar(3072)
AS
BEGIN
DECLARE @count int, @c char(1), @i int, @urlReturn varchar(3072)
SET @count = Len(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i <= @count)
BEGIN
SET @c = substring(@url, @i, 1)
IF @c LIKE '[A-Za-z0-9()''*-._! ]'
BEGIN
SET @urlReturn = @urlReturn + @c
END
ELSE
BEGIN
SET @urlReturn =
@urlReturn +
'%' +
SUBSTRING(sys.fn_varbintohexstr(CAST(@c as varbinary(max))),3,2)
END
SET @i = @i +1
END
RETURN @urlReturn
END
GO
This function works in a very similar fashion to the UrlEncodeBytesToBytesInternal found in the .NET Framework (in System.Web.HttpUtility) except that the function I wrote does not convert the URL to a byte array. If you have the latest version of Reflector, you can view the reflected code for UrlEncodeBytesToBytesInternal by going to this URL:
code://System.Web:2.0.0.0:b03f5f7f11d50a3a/System.Web.HttpUtility/UrlEncodeBytesToBytesInternal(Byte%5b%5d,Int32,Int32,Boolean):Byte%5b%5d
And speaking of Reflector – if you have the latest version, you can copy a URL link to a method, class, property, and so on by select the item and pressing Ctrl-Alt-C (which is how I got the URL to lookup the UrlEncodeBytesToBytesInternal method in Reflector).