In a recent article, I explained how to extract numbers from a string. This blog post is slightly different. In the previous article, I show how to extract single numeric values (consecutive numbers). In this article, I will show how to extract all values (non-consecutive).

This functionality is particularly useful for formatting data (when you need to export in a consistent way). For example, phone numbers (in the U.S.) are usually 10 digits long, but some people like to use (111) 222-3333 or 111-222-3333 or even 111.222.3333.

The idea here is to remove all characters that we don't want so that we can apply any formatting we do want.

Most of the time, when you see code like this, it will step through the data character by character. This code is different. It uses PatIndex to find the first occurrence of an invalid character. It removes that character, and then continues on until all instances of invalid characters are removed.

Please note that I am NOT claiming that this code is the fastest, or the most efficient way to accomplish this. I think the efficiency of this code depends on the nature of your data. If there are a lot of invalid characters in relatively long strings, this is probably not the right code to use. If there are few invalid characters in relatively long strings, then this code should be more efficient than looping over every character in the string.

The following code will remove non-alpha characters.

The following code will remove non-alpha numeric characters.
** Note: The floowing code has changed recently based on a comment from one of the readers Andrei. Originally I had a search pattern of [^a-z^0-9], which was incorrect. This search pattern incorrectly allowed the ^ to stay in the data when it should have been removed. The correct search pattern should be: [^a-z0-9]

The following code will remove non-numeric characters.

As you can see, the real work is done by using the like comparison functionality of the PatIndex function.

'%[^a-z]%'
'%[^a-z0-9]%'
'%[^0-9]%'

It should be relatively simple to modify the previous functions to suit any sort of comparisons. You can use it to remove ranges of characters, or even for single characters (similar to the replace function).

For example, if you wanted to remove certain punctuation characters, but leave everything else, you could use '%[^.^,^-]%' (to remove dot, comma, and dash).

You can use the following code to test this functionality. It creates a table variable with some sample data. The query at the end returns the original data, and another column showing the output from the three functions presented here.

Data           AlphaOnly   AlphaNumericOnly NumericOnly
---------      ----------- ---------------- -----------
(111) 222-3333             1112223333       1112223333
111-222-3333               1112223333       1112223333
111.222.3333               1112223333       1112223333
(800) XXX-3333 XXX         800XXX3333       8003333

NULL           NULL        NULL             NULL