SQL to remove characters from phone number

Question

I'm using a data warehouse and I need to remove a bunch of cases from a phone number column to be able to use it as a lookup on another data source. Some cases of what the data looks like:

888-888-8888 ---> should be 888888888
(888) 888-8888 ---> should be 888888888
888.888.8888 ---> should be 888888888
888-888-888 ext 888 ---> should be 888888888
1-888-888-888 ---> should be 888888888
888 88 8888 888888 ---> should be 888888888888888

I am able to do multiple replace statements to get rid of the simple characters like -, ., (, ). The part I'm having trouble with is removing the ' ext 888' and the '1-' where the number starts with '1-' (not contains). The 888 could be any numbers and it could be any amount of digits. Dealing with user-entered information and multiple countries.

Is there anything available that I can write in SQL that could accomplish those last 2 parts?

trim will remove leading spaces and `upper(left(f,3)) = 'EXT'` will tell you if it starts with it, or you could remove non digits. — Hogan, Nov 19 '15 at 22:23

score 1 · Accepted Answer · answered Nov 19 '15 at 22:30

1

The 'ext 888' can be fixed with:-

select STUFF('1-888-888-888 ext 888', PATINDEX('%ext [0-9][0-9][0-9]%', '1-888-888-888 ext 888'),8,'')

For the part beginning with 1, you could try:-

select STUFF('1-888-888-888',1,2,'')

In both examples you would replace the '1-888-888-888....' part with the name of your column.

answered Nov 19 '15 at 22:30

Mat Richardson

3,576
4
31
56

I was going to try that, but 'ext 888' could be anything really.. 'ext: 888', 'x888', 'ext888' etc. I guess I just have to put in a rule for every possible scenario? – user985952 Nov 19 '15 at 22:31
I would say so, yes. There's not an easy way to identify all possible combinations of this given your examples.. – Mat Richardson Nov 19 '15 at 22:33
@MatRichardson Well you can combine both query and have the perfect result, mine remove everything not numeric. – Juan Carlos Oropeza Nov 19 '15 at 22:36
Alright, guess that will work since I only have to set it up one time! Thanks – user985952 Nov 19 '15 at 22:43

SQL to remove characters from phone number

1 Answers1