Posts Tagged ‘String Manipulation’

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.