Remove Special Characters from String in SQL Server

Learn how to remove special characters from strings in SQL Server using T-SQL functions. This guide covers various methods, including using REPLACE, PATINDEX, and user-defined functions, to clean and sanitize your data efficiently.

To remove special characters from a string in SQL Server, you can use a combination of STUFF and PATINDEX.

What is STUFF in SQL Server?

The STUFF() function removes a portion of a string and then inserts another substring at a specified position.

-- Syntax
STUFF (string, start, length, replace_string)
-- Example
SELECT STUFF('C# Corner', 2, 1, 'Sharp')

What is PATINDEX in SQL Server?

The PATINDEX() function returns the position of a specified pattern within a string. If the pattern is not found, it returns 0.

-- Syntax
PATINDEX(%pattern%, string)
-- Example
SELECT PATINDEX('%#%', 'C# Corner');

Now let's see how to remove the special character from a string using STUFF and PATINDEX.

DECLARE @string AS NVARCHAR(1000) = '!C@#$Sh@arp Co#r@n*!e.\r"`#)'
DECLARE @value  AS VARCHAR(50)    = '%[^a-z A-Z 0-9]%'
WHILE PATINDEX(@value, @string) > 0
    SET @string = STUFF(@string, PATINDEX(@value, @string), 1, '')
SELECT @string

Output