SQL Server 2008 : How to Remove Special Characters in a String, STUFF and PATINDEX Examples

Hi friends, in this article I will explain about  How to Remove Special Characters in a String in SQL Server 2008   

I already explained in the previous articles about How to alter primary key column for existing table in sql server 2008, How to Get List of Stored Procedures based on table name search, using sql server 2008? and Differences and Similarities Between LEN and DATALENGTH Functions in SQL Server

PATINDEX:

It returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

For replacing special characters from string with spaces need to use PATINDEX.
Syntax:

PATINDEX ( '%pattern%' , expression )

Example:

DECLARE @Str varchar(100) SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+' SELECT PATINDEX('%SQL%', @Str)

The output of the above code as shown in the below figure.

Remove Special Characters from String in SQL Server 2008

DECLARE @regexp INT DECLARE @Str varchar(100) SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+   ' SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str) WHILE @regexp > 0 BEGIN SET @Str = STUFF(@Str, @regexp, 1, ' ' ) SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str) Print @regexp END SELECT @Str

The output of the above code as shown in the below figure.

Stuff:

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Syntax:

STUFF ( character_expression , start , length , replaceWith_expression )

Example:

DECLARE @regexp INT  DECLARE @Str varchar(100)  SET @Str='welcome to sql server'  SET @Str = STUFF(@Str, 1, 1, '@' )  Select @str

The output of the above code as shown in the below figure.