How to format unformated phone numbers Please...,

Question

How to format unformated phone numbers Please...,

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

ScottPletcher (12/22/2014)
Jeff Moden (12/22/2014)
CELKO (12/22/2014)
Pull all the punctuation out and pad with digits as needed until you get the Standard International phone number. Let the presentation layers handle presentations.
http://en.wikipedia.org/wiki/E.123
You also might want to look at a data scrubbing package to clean up this and other data.
BWAA-HAAAA... with 1 to 3 digits for a country code, 0 to 4 digits for a city code, the dialer zones of Mexico, and the NPA/NXX format in the North American Numbering plan, I'm thinking that there isn't actually a consistent standard other than using just spaces for semantical groupings. Even the Wiki article used a "+" sign to indicate a dialer prefix.
Store a formatting code for how the output needs to be. I can't image you'd need more than a tinyint for that. You still never need to store the formatting in the number itself, and you shouldn't.

I've been through this a couple of times for the telephony companies that I've worked for. There's not much difference between storing a space delimited number and trying to extract where the geographical location is by splitting up such things as country code, city code, country zone, area code, exchange, etc, etc. Yep... a tiny int would probably do for the formatting code even if you consider "special" numbers.

But telephone switches just don't work that way and neither do telephone number lookups (it would be wonderful if they did). You need to have the world database of phone numbers like those that you can purchase from CCMI or Telcordia. Even then, it takes a special "right-to-left" search to determine the geographic location of a given telephone number because there can be 1 to 3 digits provided for the country code and 0 to 4 digits provided for the city code, again, not to mention the NANPA plan and the Mexican dialing zones. It's been several years since I've had to deep dive on it and it may have changed since but the longest international telephone number I ever saw without an "extension" coming into play was 21 digits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 2

Jeff Moden (12/22/2014)
ScottPletcher (12/22/2014)
Jeff Moden (12/22/2014)
CELKO (12/22/2014)
Pull all the punctuation out and pad with digits as needed until you get the Standard International phone number. Let the presentation layers handle presentations.
http://en.wikipedia.org/wiki/E.123
You also might want to look at a data scrubbing package to clean up this and other data.
BWAA-HAAAA... with 1 to 3 digits for a country code, 0 to 4 digits for a city code, the dialer zones of Mexico, and the NPA/NXX format in the North American Numbering plan, I'm thinking that there isn't actually a consistent standard other than using just spaces for semantical groupings. Even the Wiki article used a "+" sign to indicate a dialer prefix.
Store a formatting code for how the output needs to be. I can't image you'd need more than a tinyint for that. You still never need to store the formatting in the number itself, and you shouldn't.
I've been through this a couple of times for the telephony companies that I've worked for. There's not much difference between storing a space delimited number and trying to extract where the geographical location is by splitting up such things as country code, city code, country zone, area code, exchange, etc, etc. Yep... a tiny int would probably do for the formatting code even if you consider "special" numbers.
But telephone switches just don't work that way and neither do telephone number lookups (it would be wonderful if they did). You need to have the world database of phone numbers like those that you can purchase from CCMI or Telcordia. Even then, it takes a special "right-to-left" search to determine the geographic location of a given telephone number because there can be 1 to 3 digits provided for the country code and 0 to 4 digits provided for the city code, again, not to mention the NANPA plan and the Mexican dialing zones. It's been several years since I've had to deep dive on it and it may have changed since but the longest international telephone number I ever saw without an "extension" coming into play was 21 digits.

Prior suggestions also make the assumption that full numbers, including country code, region code, etc. were provided. I could rant, but won't, about the way I used to have to deal with supplied phone number lists. Non-U.S. cell phones can also have some different dialing properties.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

sqldriver (12/22/2014)
Jeff Moden (12/22/2014)
ScottPletcher (12/22/2014)
Jeff Moden (12/22/2014)
CELKO (12/22/2014)
Pull all the punctuation out and pad with digits as needed until you get the Standard International phone number. Let the presentation layers handle presentations.
http://en.wikipedia.org/wiki/E.123
You also might want to look at a data scrubbing package to clean up this and other data.
BWAA-HAAAA... with 1 to 3 digits for a country code, 0 to 4 digits for a city code, the dialer zones of Mexico, and the NPA/NXX format in the North American Numbering plan, I'm thinking that there isn't actually a consistent standard other than using just spaces for semantical groupings. Even the Wiki article used a "+" sign to indicate a dialer prefix.
Store a formatting code for how the output needs to be. I can't image you'd need more than a tinyint for that. You still never need to store the formatting in the number itself, and you shouldn't.
I've been through this a couple of times for the telephony companies that I've worked for. There's not much difference between storing a space delimited number and trying to extract where the geographical location is by splitting up such things as country code, city code, country zone, area code, exchange, etc, etc. Yep... a tiny int would probably do for the formatting code even if you consider "special" numbers.
But telephone switches just don't work that way and neither do telephone number lookups (it would be wonderful if they did). You need to have the world database of phone numbers like those that you can purchase from CCMI or Telcordia. Even then, it takes a special "right-to-left" search to determine the geographic location of a given telephone number because there can be 1 to 3 digits provided for the country code and 0 to 4 digits provided for the city code, again, not to mention the NANPA plan and the Mexican dialing zones. It's been several years since I've had to deep dive on it and it may have changed since but the longest international telephone number I ever saw without an "extension" coming into play was 21 digits.
Prior suggestions also make the assumption that full numbers, including country code, region code, etc. were provided. I could rant, but won't, about the way I used to have to deal with supplied phone number lists. Non-U.S. cell phones can also have some different dialing properties.

Heh... right there with you on the rant. The neat thing about formatting telephone numbers is that there are so many "standards". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 4

Jeff Moden (12/22/2014)
sqldriver (12/22/2014)
Jeff Moden (12/22/2014)
ScottPletcher (12/22/2014)
Jeff Moden (12/22/2014)
CELKO (12/22/2014)
Pull all the punctuation out and pad with digits as needed until you get the Standard International phone number. Let the presentation layers handle presentations.
http://en.wikipedia.org/wiki/E.123
You also might want to look at a data scrubbing package to clean up this and other data.
BWAA-HAAAA... with 1 to 3 digits for a country code, 0 to 4 digits for a city code, the dialer zones of Mexico, and the NPA/NXX format in the North American Numbering plan, I'm thinking that there isn't actually a consistent standard other than using just spaces for semantical groupings. Even the Wiki article used a "+" sign to indicate a dialer prefix.
Store a formatting code for how the output needs to be. I can't image you'd need more than a tinyint for that. You still never need to store the formatting in the number itself, and you shouldn't.
I've been through this a couple of times for the telephony companies that I've worked for. There's not much difference between storing a space delimited number and trying to extract where the geographical location is by splitting up such things as country code, city code, country zone, area code, exchange, etc, etc. Yep... a tiny int would probably do for the formatting code even if you consider "special" numbers.
But telephone switches just don't work that way and neither do telephone number lookups (it would be wonderful if they did). You need to have the world database of phone numbers like those that you can purchase from CCMI or Telcordia. Even then, it takes a special "right-to-left" search to determine the geographic location of a given telephone number because there can be 1 to 3 digits provided for the country code and 0 to 4 digits provided for the city code, again, not to mention the NANPA plan and the Mexican dialing zones. It's been several years since I've had to deep dive on it and it may have changed since but the longest international telephone number I ever saw without an "extension" coming into play was 21 digits.
Prior suggestions also make the assumption that full numbers, including country code, region code, etc. were provided. I could rant, but won't, about the way I used to have to deal with supplied phone number lists. Non-U.S. cell phones can also have some different dialing properties.
Heh... right there with you on the rant. The neat thing about formatting telephone numbers is that there are so many "standards". 😀

My standards all dealt with how a predictive dialer, or if a cell phone, interviewer, could dial the number from their location. Lots of stuff like 'half the calls to the Dutch numbers are originating from the US and the other half is split between UK and Australia'. So began my journey to figure out how to dial Dutch numbers from three different countries, assuming their validity based on length and existing leftmost digits, etc.

Italy though. Italy was my least favorite.

http://en.wikipedia.org/wiki/List_of_dialling_codes_in_Italy

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 5

So, fundamentally what is being said (or acknowledged) is that this is NOT a table of phone numbers. It is actually a bunchO'stuff table that may contain phone numbers. Using some combination of pattern matching, nested IFs, AI, fuzzy logic and crossed fingers, we may be able to extract an actual phone number from the column, but there are no guarantees.

Don Simpson

I'm not sure about Heisenberg.