How to remove special characters from a string in MS SQL Server (T-SQL)

If you only have to remove a few specific special characters from a string value, the REPLACE function can be used, e.g.:

select replace( replace( stringvalue, '-', ''), ',', '')

For a more general solution, the user-defined function below may be used to filter out all special characters from a string value.

---

-- Removes special characters from a string value.

-- All characters except 0-9, a-z and A-Z are removed and

-- the remaining characters are returned.

-- Author: Christian d'Heureuse, www.source-code.biz

create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)

with schemabinding

begin

if @s is null

return null

declare @s2 varchar(256)

set @s2 = ''

declare @l int

set @l = len(@s)

declare @p int

set @p = 1

while @p <= @l begin

declare @c int

set @c = ascii(substring(@s, @p, 1))

if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122

set @s2 = @s2 + char(@c)

set @p = @p + 1

end

if len(@s2) = 0

return null

return @s2

end

---

Example of how to use the function:

select dbo.RemoveSpecialChars('abc-123+ABC')

Result:

abc123ABC