Home > SQL Server, T-SQL > T-SQL Strip Invalid Characters

T-SQL Strip Invalid Characters

Occasionally I need to strip invalid characters from a string and the requirements are often subtly different, sometimes we want numbers only, other times numbers and decimal points, and maybe only letters.

We’ve probably all had to do this at some point and probably grabbed something off the internet, but I finally took the time to write a more generic and performant version as listed below which allows for a valid character pattern to be passed in.

Create Function dbo.StripInvalidCharacters
    Strips invalid characters from a string. This is useful for cleaning up
    strings such as telephone numbers or human entered values such as
    '10.7 miles'
        The string to strip characters from.
        A search pattern that identifies invalid characters. Some example are:
        - '0-9'    - numbers excluding decimal point
        - '0-9.'   - numbers including decimal point
        - 'a-z'    - letters only
        - 'a-f0-9' - hexadecimal only
    10-May-2016 S.Wilber
    @input varchar(1000)
   ,@valid_character_pattern varchar(100)
Returns varchar(1000)
    While PatIndex('%[^' + @valid_character_pattern + ']%', @input) > 0
        Set @input = Stuff(@input,PatIndex('%[^' + @valid_character_pattern + ']%', @input), 1, '')     

    -- return
    Return @input

Feel free to copy and re-use. If you spot any improvements, please let me know and I’ll update this post.

  1. No comments yet.
  1. No trackbacks yet.

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 )

Facebook photo

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

Connecting to %s

%d bloggers like this: