Unescape in SQL Server 2000

[otalens]

Hi,

I had some problems unescaping data retrieved from an internet page in SQL Server.
While SQLServer 2000 has the possibility to create User-defined functions, I created two functions to unescape a string like '%200test%205' before inserting it into the database.
I hope this will help some of you.


CREATE FUNCTION fn_Unescape
/* Unescaping funtion. Unescapes escaping characters starting with a '%'
pre: @charstring is string to convert containing escaping characters
post: @retstring is string without escaping characters
*/
(
@charstring AS varchar(8000)
)
RETURNS varchar(8000)
AS

BEGIN

DECLARE @retstring AS varchar(8000)
DECLARE @encodedchar AS varchar(6)
DECLARE @pos AS int
DECLARE @decValue1 AS int
DECLARE @decValue2 AS int

SET @retstring = @charstring
SET @pos = CHARINDEX('%', @retstring)

WHILE @pos > 0
BEGIN
SET @encodedchar = SUBSTRING(@retstring, @pos, 3)
SET @decValue1 = dbo.fn_HexCharToInt(@encodedchar,2)
SET @decValue2 = dbo.fn_HexCharToInt(@encodedchar,3)

SET @retstring = REPLACE(@retstring, @encodedchar,
CHAR( (@decValue1 * 16) + @decValue2) )

SET @pos = CHARINDEX('%', @retstring)
END

RETURN @retstring
END



CREATE FUNCTION fn_HexCharToInt
/* Conversion from character representing hexadecimal value to decimal value
pre: @encodedchar is string(3) in range '0'-'9' and 'A'- 'F'
@intPos is position in string, range 1-3
post: @decValue is decimal value, range 0-15
*/
(
@encodedchar as varchar(3),
@intPos as int
)
RETURNS int AS
BEGIN
DECLARE @decValue as int
IF ( SUBSTRING(@encodedchar, @intPos, 1)>= 'A') and ( SUBSTRING(@encodedchar, @intPos, 1)<= 'F')
--Character is not a numeric value
SET @decValue = CONVERT(int,CONVERT(varbinary,SUBSTRING(@encodedchar, @intPos, 1))) - 55
ELSE
--Character is a numeric value
SET @decValue = CONVERT(int,CONVERT(varbinary,SUBSTRING(@encodedchar, @intPos, 1))) - 48

RETURN @decValue
END



Regards,

Oliver Talens
*****************************
Abyss I.T. Solutions
Mathildastraat 36B
4901 HC Oosterhout

http://www.Abyss.nl

http://www.PanelPoint.nl

tel: +31 (0)162-439809
fax: +31 (0)162-439882
O.Talens@Abyss.nl
*****************************