Escape() function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Escape() function

Author

Topic

shakib
Starting Member

1 Post

Posted - 2006-05-25 : 16:09:59

I am trying to write a function that emulates the VB Escape() function (spaces will be escaped to %20...).
So far I have the following (it works 90% of the time):

CREATE FUNCTION EscapeChars
(@Input VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @Result VARCHAR(256)
SET @Result = ''
DECLARE @i INTEGER
SET @i = 1
WHILE @i <= DATALENGTH(@Input)
BEGIN
DECLARE @aChar INTEGER
SET @aChar = ASCII(SUBSTRING(@Input, @i, 1))

IF (@aChar >= 48 AND @aChar <= 57) OR (@aChar >= 64 AND @aChar <= 90) OR (@aChar >= 97 AND @aChar <= 122) OR (@aChar in (42, 43, 45, 46, 47, 95)) -- * + - . / _
BEGIN
SET @Result = @Result + CHAR(@aChar)
END
ELSE
BEGIN
SET @Result = @Result + '%' + SUBSTRING('0123456789ABCDEF',@aChar/16+1,1)+
SUBSTRING('0123456789ABCDEF',@aChar%16+1,1);
END
SET @i = @i + 1
END
RETURN @Result
END

The problem I am seeing is that some characters need to be replaced by %uxxxx instead of %xx. But I am not which and when to do the replacement.
I am looking forward for any suggestion.
Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-26 : 03:50:40

Going through each character will be very slow.
Have a look at patindex - it allows you to just retrive the next value from a list in a string.
You can then use a case statement to replace the string.

something like this.
Also probably better if you do a replace then you only have to deal with each value once - depends on your datatype though.

select @s = '%[^' + char(48) + '-' + char(57) + char(64) + '-' + char(90) + ']%'
select @i = patindex(@s,@str)
while @i <> 0
select @new = substring @str,@i,1)
select @new = case @new
select @str = stuff(@s,@i,1,'%' + SUBSTRING('0123456789ABCDEF',ascii(@new)/16+1,1)+
SUBSTRING('0123456789ABCDEF',ascii(@new)%16+1,1))
select @i = patindex(@s,@str)
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources