WilberBeast

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.