It is a common challenge for T-SQL developers to remove special ASCII characters as REPLACE T-SQL function does not remove it. CHAR and ASCII functions can assist replacing special ASCII Characters. Let's jump to the solution.
T-SQL Script
-- Declare the variables
Declare @original_string varchar(100)
Declare @clean_string varchar(100)
-- Build input string combining ascii and special ascii characters
Set @original_string = 'TEST'
Set @original_string = CHAR(1) + @original_string -- add special ascii character
Set @original_string = CHAR(2) + @original_string -- add special ascii character
Set @original_string = CHAR(3) + @original_string -- add special ascii character
Set @original_string = CHAR(4) + @original_string -- add special ascii character
Set @original_string = CHAR(10) + @original_string -- add special ascii character
Set @original_string = CHAR(13) + @original_string -- add special ascii character
Set @original_string = @original_string + '-done' -- add special ascii character
Set @clean_string = '' --
DECLARE @special_string VARCHAR(2);
DECLARE @counter INT= 1;
WHILE @counter <= Len(@original_string)
BEGIN
IF(ASCII(SUBSTRING(@original_string, @counter, 1)) > 32)
BEGIN
Set @clean_string = @clean_string + CHAR(ASCII(SUBSTRING(@original_string, @counter, 1)))
End
SET @counter = @counter + 1
END
Select @clean_string
Select @original_string
Output
I frequently, use this script for special character cleansing. Hope it will also be helpful for you.
Happy T-SQLing!