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.