Wednesday, December 29, 2010

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,
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
with schemabinding
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
if len(@s2) = 0
return null
return @s2

Example of how to use the function:
select dbo.RemoveSpecialChars('abc-123+ABC')

No comments:

Post a Comment