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
/******************************************************************************
Description:
    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'
In:
    @input
        The string to strip characters from.
    @valid_character_pattern
        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
History:
    10-May-2016 S.Wilber
        Created
******************************************************************************/
(
    @input varchar(1000)
   ,@valid_character_pattern varchar(100)
)
Returns varchar(1000)
As
Begin
    While PatIndex('%[^' + @valid_character_pattern + ']%', @input) > 0
        Set @input = Stuff(@input,PatIndex('%[^' + @valid_character_pattern + ']%', @input), 1, '')     

    -- return
    Return @input
End

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

Advertisements
  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 )

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: