November 9, 2004 at 3:32 pm
I have a question for you:
I currently have a database with a client table where I store the phone number in it. The problem I have is that the company I work for are going on the Mexico market. So right the phone number field is storing the phone number like : 5551231234 for USA and Canada. But the Mexico phone are like 12 12 1234 1234.
What's the best way to store the phone number for the client's table? int or varchar(12)? (the database is currently up and running, so I cannot really change the table (or add another one), but I can change the field type...)
Thanks
November 9, 2004 at 7:43 pm
Excuse my English ignorance, but is 12 the country code for Mexico? If so, wouldn't you want to store these numbers as + 12 ... ? If so, a varchar is the only way I can think of without creating more fields. Of course, that means you lose a certain amount of default field validation ....
This also helps with any numbers that start with a zero - as any leading zeroes would not be stored or displayed (by default, anyway) if the field is defined as int.
Note also that 2,147,483,647 is the maximum value that can be stored as an int field - so I think you would have to use a bigint.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 9, 2004 at 10:30 pm
Store it in a varchar field.
What if the number is 0800 COFFEE?
If its varchar it can save telephone numbers in all formats.
Regards
Anton
November 10, 2004 at 12:59 am
I'd store it in a varchar field. That will allow you to international code prefix it, and use other local formatting things that people from other countries use.
here in australia we write a local number as (02) 9900 0000 but the same number as an international number we write as +61 2 9900 0000 Then again, if you wanted to add an extension number you might want to write it as +61 2 9900 0000 # 344
There's lots of different ways to format a phone number... ultimately when you punch it into a phone you ignore all the formatting and just use the digits. But different countries have different phone number lengths, to be safe I usually declare phone number columns as a varchar(32): plenty of space for any type of formatting.
Julian Kuiters
juliankuiters.id.au
November 10, 2004 at 2:18 am
We store all our phone numbers as varchar fields in the international number.
So British numbers would be (44) 1234 567890 and
Mexico would be (52) 1234567890
If u need to see the numbers without a space (say for comparision purposes), then you can use a replace command.
Another point is you never need to do maths with phone numbers, so they don't need to be int.
November 10, 2004 at 2:44 am
I always use a user defined type that equates to VARCHAR(20) and specifies that the phone number must contain at least one numeral.
Things that get put in telephone number fields
Personally I like to separate out the country code, telephone number and extension number into separate fields simply to aid searching.
Even by doing this my office number is can be entered as
If telephone number searching is required you quickly find that you need two fields for the telephone number. One for how it displays and one for the format in a searcheable format.
November 10, 2004 at 5:09 am
A good rule of thumb is if you're not going to do any mathmatical calculations on a stored field that contains numbers, then store it as a text field. I use varchar(x) for all phone numbers.
November 10, 2004 at 5:55 am
I'm not sure that I agree with non-maths numbers stored in text.
I would say that the rule of thumb would be that if a numerical value has to be displayed, stored or retrieved in a specific non-numerical format then use text fields.
An integer field consumes 4 bytes and covers a vast range of values so if I am storing numerical values I try and stick with numerical fields.
November 10, 2004 at 7:09 am
We debated long and hard, but went with integer for NANP (North American Numbering Plan) numbers, and varchar for other numbers.
Given the different numbering system and standards around the world, the international numbers are a mess (it's all but impossible to validate the data), while the NANP numbers are reasonably clean.
P
November 10, 2004 at 7:57 am
Those standards sounds interesting Joe. Any help finding these standards would be appreciated. It would help talking with development teams about what data should or should not be stored in certain ways.
Francis
October 18, 2005 at 7:01 pm
Im in the same boat. We need to store N. American numbers. I was thinking of doing it in 2 columns. Is this the best way to do it?
areacode int 3
phonenumber int 7
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply