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 