Extract only numbers from phone number field (Solved)

[Revan]

Hey all!
I'm working on a report where I'm pulling phone numbers from a relational database. Unfortunately, there is no standard format for these numbers, so I can't do anything with them. Formats include:
9998887777
999-888-7777
999_888_7777
(999) 888-7777
999 888 7777
and probably some other formats I haven't seen yet. Is there any way to just strip everything that isn't 0-9 from this field, and just return it in 9998887777 format?

Thanks!

[New_Guy]

Hi,
This is better to do it in the ETL or on the database side, but if you cant get that done, try the below case statement for the maximum length that is there for the phone number field.

Good luck
New guy


case
when substring([Data Item1],1,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],1,1)
else '' end +
case when
when substring([Data Item1],2,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],2,1)
else '' end +
case when
when substring([Data Item1],3,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],3,1)
else '' end +
case when
when substring([Data Item1],4,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],4,1)
else '' end +
case when
when substring([Data Item1],5,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],5,1)
else '' end +
case when
when substring([Data Item1],6,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],6,1)
else '' end +
case when
when substring([Data Item1],7,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],7,1)
else '' end +
case when
when substring([Data Item1],8,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],8,1)
else '' end +
case when
when substring([Data Item1],9,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],9,1)
else '' end +
case when
when substring([Data Item1],10,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],10,1)
else '' end + 
case when
when substring([Data Item1],11,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],11,1)
else '' end + 
case when
when substring([Data Item1],12,1) in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') then substring([Data Item1],12,1)
else '' end

[Revan]

Oh good lord is that ugly. But that'll work, for sure. I wish I could get it done on the database side, it'd make alot of things easier. I'd thought of using substring to extract the other characters, replacing them with '', but I was afraid I'd miss some random other character that someone had thrown in, like a tilde or something else. This gets around that issue. Thanks for this. I really appreciate it.

[New_Guy]

Hi,
Ask your sql developer to create a function atleast and it will be much easier.
Good luck
New guy

[Lynn]

You didn't mention what database you are using. There may be a replace or a translate function available.

[sjdig]

What about using a nested replace function when you identify all the characters that you'd like to remove from the field?

I tried the below on the field in my database that included parentheses and dashes and was able to get it to work.

Code Select Expand

replace(replace(replace([Formatted Phone Number],'(',''),')',''),'-','')

Maybe there's a way to use this in conjunction with another function or something to identify the numbers first and only strip non number values so that you don't have to nest many replace functions? Perhaps someone more experienced can chime in.

[sjdig]

I guess if you wanted to work backwards to quickly identify the non number values, you could also use the replace function.

Code Select Expand

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace([Formatted Phone Number],'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')

That would strip out all the numbers and only leave you with the non number values.

The more I thought about it, I realized you could use the same function to get the list for what was needed in the previous nested replace function that I had posted.

[Revan]

@lynn we use a Netezza SQL database. Or so our IT people tell me.
@lynn & @sjdig If I'm understanding you correctly, using the replace function I would have to identify every non-numeric character, and list that in the function. Which leaves the possibility of missing something weird, like someone using a tilde or a letter (I work for the government. It happens) in the phone number. New_Guy's suggestion guarantees that I only return the numbers, which makes it preferable.

I'm not sure about the transpose. Would it operate similarly to the replace function?

[CognosPaul]

Netezza had a regexp_replace function.

You can match all non-numeric characters with /d or [^0-9].

I don't have a netezza instance to test, so you'll have to do some experimenting. This is probably the safest way to get the actual numbers.