Today, I am going to tell you how to replace the special characters in a string with spaces. In this case, I need to use PATINDEX.

PATINDEX
It will 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. And this is the code that I used:
PATINDEX ( '%pattern%' , expression )

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

Here is the result from that code:

Remove Special Characters from String in SQL Server 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

Result:

STUFF
This 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. This is the code:
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