SQL Server | Remove Special Characters

I needed to remove all the special characters from a field in a particular table in SQL Server to be used for searching purposes. After searching (binging :P)… I found the next SQL Server Function which did the trick.

This Function Removes any special character from the string value passed. All characters except 0-9, a-z and A-Z are all removed, whilst the remaining characters are returned back.

ALTER FUNCTION dbo.RemoveSpecialCharacters
(
@s VARCHAR(256)
)
RETURNS VARCHAR(256) WITH SCHEMABINDING

BEGIN
IF (@s is null)
RETURN null

DECLARE @result varchar(256)
SET @result = ”

DECLARE @length int
SET @length = len(@s)

DECLARE @p int
SET @p = 1

WHILE @p <= @length 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 @result = @result + CHAR(@c)

SET @p = @p + 1

END

IF LEN(@result) = 0
RETURN NULL

RETURN @result
END

Happy Cleaning!!! 🙂

References from here by Christian d’Heureuse

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: