Today, we are going to see a scenario where we need to remove special characters from a string in SQL Server. Let us discuss on few methods to achieve this “extended” trim functionality.
1. Option: Using REPLACEREPLACE function can be effectively used to replace the special characters.
Script:
DECLARE @BaddataString NVARCHAR(max);
SET @BaddataString = 'my '+
CHAR(10)+CHAR(10)+CHAR(10) +
'text ' +
CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) +
' ' +
CHAR(10)+CHAR(10)+CHAR(10);
SELECT
@BaddataString,
LEN(@BaddataString)
, REPLACE(REPLACE(REPLACE(RTRIM(@BaddataString),CHAR(9), ''),CHAR(10),''),CHAR(13),'') ,
LEN(REPLACE(REPLACE(REPLACE(RTRIM(@BaddataString),CHAR(9), ''),CHAR(10),''),CHAR(13),''))
Here is the output of the below code:
Script
/*
The below function replaces special characters like invisible TAB, Carriage Return, and Line Feed characters.
*/
CREATE FUNCTION dbo.FN_RemoveBadCharacter(@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN (SELECT CAST('' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)'));
END
GO
DECLARE @BaddataString NVARCHAR(max);
SET @BaddataString = 'my '+
CHAR(10)+CHAR(10)+CHAR(10) +
'text ' +
CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) +
' ' +
CHAR(10)+CHAR(10)+CHAR(10);
SELECT @BaddataString,
LEN(@BaddataString)
, dbo.FN_RemoveBadCharacter(@BaddataString),
LEN(dbo.FN_RemoveBadCharacter(@BaddataString))
Here is the output of the below code:
There are certain things its best to do at application code rather than T-SQL. SQL Server may not be the best place to formatting string, date etc. There will be lots of available resources just away from a google search.
Hope, you enjoy this post, please share your thoughts as always!
SQLZealots 
One thought on “How to remove special characters like TAB, Carriage Return, and Line Feed characters from string in SQL Server”