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:
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 removeThere 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!