ufs_URLEncode Scalar-Valued Function

by Bill Dowling 29. July 2010 08:14

Returns encoded URL line. For example, all spaces are replaced with %20.

USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufs_URLEncode](@url varchar(1024))
RETURNS varchar(3072)
AS
BEGIN
    -- Peter DeBetta's SQL Programming Blog
    -- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urlencode.ASpx
    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()''*-._! ]'
            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

Tags:

SQL Coding Examples

Add comment

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List