International Phone Formats - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

International Phone Formats

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 11:53:33

Being in the US Market, we have only had to deal with (000) 000-0000 phone formats. We need to start encorporating our international business partners as part of our distributed phone directory for the company.

Does anyone have any experience with storing the various formats of phone numbers from various countries? We're hoping to avoid free form text.

I found this site, but it doesn't mention a format per country. Does one standard even exist?

http://www.wtng.info/wtng-jj.html#J00

Thanks

Brett

8-)

Edited by - x002548 on 04/09/2003 11:56:02

macka
Posting Yak Master

162 Posts

Posted - 2003-04-09 : 12:05:27

Not really an answer to your question, but these people seem to be responsible for issuing international dialling codes. Maybe some useful links from there.

[url]http://www.itu.int/home/[/url]
[url]http://www.itu.int/ITU-T/inr/nnp/index.html[/url]

Also, check out:

[url]http://kropla.com/dialcode.htm[/url]
[url]http://www.numberingplans.com/[/url]
[url]http://www.numberplan.org/[/url]

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Edited by - macka on 04/09/2003 12:17:02

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-09 : 13:02:28

Brett,

I'd be interested if you were to post the final structure that you use for international numbers.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 13:17:39

So would I....

I did find this following page..,it appears that it's more than just a particular number. For example it depends if you are in the country of origin, or external to it. So what you store is a matter of a composite of things...country code, city code, ect, and THEN the determination if you want to see it from a national (the country you're in and the phone number it belongs to) or international.

Take a peek:

http://www.numberingplans.com/index.php3?goto=guide&topic=E123

Brett

8-)

Edited by - x002548 on 04/09/2003 13:18:52

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-09 : 14:53:50

Hey, E.123 looks great. (lots of dialing exceptions though)

When you read it and come up with a recommendation, I wonder if it'll be any different than 3 columns Country, City, Phone ?

Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-09 : 15:05:13

I think Country code, City code and Phone number pretty much covers it all. The only thing you cant count on is the number of digits in each set (no pun intended

). Have you thought about cell phone numbers? I think the city code should cover the Cell Operator code. Here in the UAE we have only one operator, so all mobile phone numbers are +971 50 xxx xxxx. But in India there are many companies in the cell market and each company has one or more prefixes:
Company 1: +91 98200 xxxxx / 98210 xxxxx
Company 2: +91 98242 xxxxx
and so on...

OS

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 15:42:36

Thanks mohdowais

Just curious, what format of phone number do you see when calling locally?

Also, not that this helps much, but there's this bizzaro concept I ran across called phantom columns. Has anyone heard of such a thing?

http://208.56.195.170/Format.htm

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 16:35:02

Well it seems Microsoft doesn't have an answer either...just free form it:

http://www.microsoft.com/globaldev/getwr/steps/wrg_telnum.mspx

Brett

8-)

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-04-09 : 17:05:17

Why not keep country codes, area codes, and phone numbers in separate fields like they suggest? All you would need to do is strip any non numeric values from the inputs before inserting, and store each in a varchar(25) or so. That allows you to keep numbers that may or may not have leading zeros. That should do it for the storage problem and allows users to enter the number in whatever format they fancy. As for display, you would just need to write a function in your application to display the appropriate format based on the country. You could code up the countries currently in the system, and have a default format for eveything else. Then you'd just need to add formatting code as new countries were added to the phone book... HTH

Jeff Banschbach
Consultant, MCDBA

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 17:11:21

Yeah, that's the path I'm heading...One small problem.

I can't find anyplace that contains a list of the format per country...I've been surfing all day.

I was even thinking about store the actual code for the java developer, and then pass it to him/her along with the data...sort of keeping a phone format rule table (probably just stored in the country table).

That's what I'd like to do:

Found another blurb from Microsoft...but it's far from complete.

http://www.microsoft.com/globaldev/getwr/steps/wrg_telnum.mspx

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-09 : 18:11:12

http://www.numberingplans.com/index.php3?goto=guide&topic=ccs

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 20:58:48

Mike,

Thanks...been there. I don't see where it references formatting as regards to the country. see previous post. MS indicate that each country identifies it's phones with specific chars...ie . / - ect.

I'm only dealing with Ireland, Japan, Poland and Brazil...

Did I mention I hate outsoucing?

I'm thinking about a separate table to handle these...2,000 rows as comapred to 120,000...but the concenus is to make it one. Not to mention that it will involve opening up numerous batch programs (cobol/mq series) as compared to creating a new table, new view and have no (hopefully) any impact on any of the existing procedures....

maybe I should pump gas.

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-10 : 02:38:44

If you are dealing with only four countries, it shouldnt be such a headache to find the relevant formats. But if its going to be more than that, IMHO to hell with formatting, just give 'em the numbers!

In most of the Middle East and the Indian Subcontinent, there is no concept of a telephone number format. All that we need to know is the country code, city code and the phone number. But then, we also have flying carpets and talking camels

EDIT: Brett, all telephone numbers here are seven digits and every city has a one or two digit code. To call another city, add a zero to the city code (i guess that's the same everywhere).
e.g.
calling locally: 123 4567
from another city: 04 123 4567
from another country: +971 4 123 4567

OS

Edited by - mohdowais on 04/10/2003 02:43:23

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-10 : 08:56:49

Great, Thanks for the info....love the talking camels part...how do I normalize that? Also what about talking Yaks? Anyway, Me thinks we are going with Country Code, Area Code, and Phone in separate columns...I'll worry about displays later....

Thanks every one.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 10:32:03

OK, For anyone interested. We've decided to leave Intl Phone numbers unformatted (for the present) because there appears to be no standard format, even within a country. For example Ireland, the format will be changing for some (not all). Like:

More info found at:

http://www.wtng.info/wtng-ii.html#Ireland

Still if anyone has any advice, I'd be very appreciative.

From www.wtng.info

New Code Permissive date Mandatory date
======== =============== ==============
+353 46 1 August 2002 1 October 2003
+353 56 1 September 2002 1 October 2003
+353 59 1 September 2002 1 October 2003
+353 71 1 October 2002 1 December 2003
+353 74 1 October 2002 1 December 2003
+353 90 1 October 2002 1 December 2003
+353 94 1 October 2002 1 December 2003

The following table indicates places that are subject to the 2002-2003
numbering changes, with existing numbering, and migration to the
new numbering (in international format):

Place Existing Number New Number
===== =============== ==========
Athlone +353 902 xxxxx +353 90 64 xxxxx
Athy +353 507 xxxxx +353 59 86 xxxxx
Ballaghaderreen +353 907 xxxxx +353 94 98 xxxxx
Ballinasloe +353 905 xxxxx +353 90 96 xxxxx
Ballinrobe +353 92 xxxxx +353 94 95 xxxxx
Ballymote +353 71 xxxxx +353 71 91 xxxxx
Baltinglass +353 508 xxxxx +353 59 64 xxxxx
Banagher North +353 902 xxxxx +353 90 64 xxxxx
Banagher South +353 509 xxxxx (no change for 2002-2003)
Birr +353 509 xxxxx (no change for 2002-2003)
Boyle +353 79 xxxxx +353 71 96 xxxxx
Buncrana +353 77 xxxxx +353 74 93 xxxxx
Carlow +353 503 xxxxx +353 59 91 xxxxx
Carrick-on-Shannon +353 78 xxxxx +353 71 96 xxxxx
Castlebar +353 94 xxxxx +353 94 90 xxxxx
Castlecomer +353 56 xxxxx +353 56 44 xxxxx
Castlerea +353 907 xxxxx +353 94 96 xxxxx
Claremorris +353 94 xxxxx +353 94 93 xxxxx
Donegal +353 73 xxxxx +353 74 97 xxxxx
Drumshanbo +353 78 xxxxx +353 71 96 xxxxx
Dungloe +353 75 xxxxx +353 74 95 xxxxx
Edenderry +353 405 xxxxx +353 46 97 xxxxx
Enfield +353 405 xxxxx +353 46 95 xxxxx
Freshford +353 56 xxxxx +353 56 88 xxxxx
Kells +353 46 xxxxx +353 46 92 xxxxx
Kilkenny +353 56 xxxxx +353 56 77 xxxxx
Letterkenny +353 74 xxxxx +353 74 91 xxxxx
Lifford +353 74 xxxxx +353 74 91 xxxxx
Manorhamilton +353 72 xxxxx +353 71 98 xxxxx
Muine Bheag +353 503 xxxxx +353 59 97 xxxxx
Navan +353 46 xxxxx +353 46 90 xxxxx
Nobber +353 46 xxxxx +353 46 90 xxxxx
Portumna (Part) +353 509 xxxxx +353 90 97 xxxxx
Ramelton +353 74 xxxxx +353 74 91 xxxxx
Roscommon +353 903 xxxxx +353 90 66 xxxxx
Sligo +353 71 xxxxx +353 71 91 xxxxx
Swinford +353 94 xxxxx +353 94 92 xxxxx
Trim +353 46 xxxxx +353 46 94 xxxxx

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-14 : 23:37:54

quote:
because there appears to be no standard format, even within a country. For example Ireland...

Ahhhhhh, those Irish, they ARE the perfect example of people who can never agree on anything...

Don't yell at me too hard, my grandpa was Irish, I know what I'm talking about.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-04-15 : 13:50:03

Aaaaaaah....those 3G people..."my grandpa was Irish"...
however...the those of the 1st generation have improved on the old model...technology in reverse!!

Good day to you from Warm, Dry and Sunny Dublin.....now isn't a strange thing to be able to say in the middle of April!!

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 13:54:23

Didn't mean to rag on the Irish (I'm a quarter Irish...we just dont talk about it). Just pointing our problem.

I'm just curious that no one has any background with this problem.

quote:

Good day to you from Warm, Dry and Sunny Dublin.....now isn't a strange thing to be able to say in the middle of April!!

Come on Andrew, you're just pulling our leg, ain'tcha

PS You know why God inveted wiskey don't you?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-15 : 15:00:15

Brett,

quote:
We've decided to leave Intl Phone numbers unformatted (for the present)

What do you mean unformatted? VARCHAR?

Sam

Fixed the quote...

Edited by - SamC on 04/15/2003 15:13:53

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 15:17:53

Yup...but also stripping out any special chars a user might enter, or from feeds.

That is unless someone has a better suggestion.

I'm All ears

ps does anyone know why god inveted whiskey?

Brett

8-)

bm1000
Starting Member

37 Posts

Posted - 2003-04-15 : 15:37:19

Q. Why did God invent whiskey?
A. To prevent the Irish from ruling the world!

quote:

Yup...but also stripping out any special chars a user might enter, or from feeds.

That is unless someone has a better suggestion.

I'm All ears

ps does anyone know why god inveted whiskey?

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources