>SQL Server: How to Remove Special Characters


>

One more useful function from my query bank, which I like to use to remove special characters from a string.
CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )
RETURNS VARCHAR(8000)  
BEGIN
    IF @InputString IS NULL
        RETURN NULL
    DECLARE @OutputString VARCHAR(8000)
    SET @OutputString =
    DECLARE @l INT
    SET @l = LEN(@InputString)
    DECLARE @p INT
    SET @p = 1
    WHILE @p <= @l
        BEGIN
            DECLARE @c INT
            SET @c = ASCII(SUBSTRING(@InputString, @p, 1))
            IF @c BETWEEN 48 AND 57
                OR @c BETWEEN 65 AND 90
                OR @c BETWEEN 97 AND 122
                  –OR @c = 32
                SET @OutputString = @OutputString + CHAR(@c)
            SET @p = @p + 1
        END
    IF LEN(@OutputString) = 0
        RETURN NULL
    RETURN @OutputString
END
How to use it.
SELECT dbo.RemoveSpecialChars (‘This string contains special chracters:/ Which * & we % need @ to #remove’)
According to this function space is a special character and if you want to ignore spaces then uncomment “OR @c = 32”.
Advertisements

Posted on May 20, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 1 Comment.

  1. Very nice and timely for me. Works perfectly. Thank you.R Baum

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: