I am unsure what the format is with US/CAD numbers, but I store the phone numbers into my databases as varchar. I normally use varchar(12).
The best way to do it is have filtering on the input data so that it converts a string into your own standard format for saving to the db, and then similarly converts it into a standard viewing format on retrieval.
I am from Australia and our landline numbers are in the format (0x) xxxx xxxx
In a database I will accept any input and reg check it to ensure it's in the expected format before stripping it down to 0xxxxxxxxx for saving into the database.
Then on retrieval I'll run it through a function that accepts this mysql format and prints it nice and pretty on the screen such as (0x) xxxx-xxxx
For this reason it ensures my users can enter a phone number into the field using any combination of spaces and dashes and brackets that they want (as long as there is a 2 digit area code and an 8 digit number, and the data will be saved to the db in the same format everytime and displayed on the page in the same way each time.
This will make searching for a phone number in your db a lot easier.
Main Topics
Browse All Topics
by: Raynard7Posted on 2006-10-18 at 15:49:30ID: 17761935
Hi,
The way you store them depends on how you are going to use them.
I would not store them as numbers per se but as var char's because otherwise leading zeros may be truncated - and it would be harder to preserve formatting.
It all depends on the source and what you are doing with them - if you are going to get people to insert them themselves for their own later reference then you may just want to store the nubmers already formatted into one field.
If you want to run an automatic dialler application with these numbers I would store these with only the numbers, one column for country code, one for the local area code when calling internally, one for the local area code when coupled with international code and one for the phone number.
This should cover all eventualities - as I said the usage dictates the storage method