Thursday, May 30, 2013

Delete ASCII Characters from the string

Create FUNCTION dbo.Remove_ASCII_Char (@CharData varchar(200)) 
RETURNS varchar(200)
AS 
BEGIN
DECLARE @Index INT,
@strASCII varchar(8000),
@strASCIIChar INT
 
SET @Index = 1
SET @strASCII = ''

WHILE @Index < LEN(@CharData)+1
BEGIN
SET @strASCIIChar = ASCII(SUBSTRING(@CharData, @Index, 1))
IF @strASCIIChar not BETWEEN 1 and 32 
BEGIN
SET @strASCII = @strASCII + CHAR(@strASCIIChar)
END
SET @Index = @Index + 1
END
RETURN @strASCII
END