Tag: Remove special characters in SQL Server

SCRIPT GALLERY, SQL

How to remove special characters like TAB, Carriage Return, and Line Feed characters from string in SQL Server

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 REPLACE

REPLACE 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:

2. Option: Using CDATA Another effective option to use CDATA method.

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:

3. Option: Using CLR to define a function to remove

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!