[mysql] How to store phone numbers on MySQL databases?

Possible Duplicate:
mysql datatype for telephne number and address

Any suggestions on best practice to store telephone numbers in a DB? Consider a US phone number:

• 555 555 1212
• 555-555-1212
• (555) 555 1212
• 5555551212
• 1-555-555-1212
• 1 (555) 555-1212
• and so on ...

Should I remove formatting and store only numbers? Should I just use one field -- or split them up into: country code, area code, phone number, etc.? Suggestions?

I would definitely split them. It would be easy to sort the numbers by area code and contry code. But even if you're not going to split, just insert the numbers into the DB in one certain format. e.g. 1-555-555-1212 Your client side will be thankfull for not making it reformat your numbers.

varchar, Don't store separating characters you may want to format the phone numbers differently for different uses. so store (619) 123-4567 as 6191234567 I work with phone directory data and have found this to be the best practice.

I would recommend storing these as numbers in columns of type varchar - one column per "field" (like contry code etc.).

The format should be applied when you interact with a user... that makes it easier to account for format changes for example and will help esp. when your application goes international...

I would suggest a varchar for the phone number (since phone numbers are known to have leading 0s which are important to keep) and having the phone number in two fields:

Country Code and phone number i.e. for 004477789787 you could store CountryCode=44 and phone number=77789787

however it could be very application specific. If for example you will only store US numbers and want to keep the capability of quickly performing queries like "Get all the numbers from a specific area" then you can further split the phone number field (and drop the country code field as it would be redundant)

I don't think there is a general right and wrong way to do this. It really depends on the demands.

Form my point of view, below is my suggestions:

1. Store phone number into a single field as varchar and if you need to split then after retrieve split accordingly.
2. If you store as number then preceding 0 will truncate, so always store as varchar
3. Validate users phone number before inserting into your table.

You can use varchar for storing phone numbers, so you need not remove the formatting

I would say store them as an big integer, as a phone number itself is just a number. This also gives you more flexibility in how you present your phone numbers later, depending on what situation you are in.

Suggest that you store the number as an extended alphanumeric made up of characters that you wish to accept and store it in a varchar(32) or something like that. Strip out all the spaces , dashes, etc. Put the FORMATTING of the phone number into a separate field (possibly gleaned from the locale preferences) If you wish to support extensions, you should add them in a separate field;

I suggest storing the numbers in a varchar without formatting. Then you can just reformat the numbers on the client side appropriately. Some cultures prefer to have phone numbers written differently; in France, they write phone numbers like 01-22-33-44-55.

You might also consider storing another field for the country that the phone number is for, because this can be difficult to figure out based on the number you are looking at. The UK uses 11 digit long numbers, some African countries use 7 digit long numbers.

That said, I used to work for a UK phone company, and we stored phone numbers in our database based on if they were UK or international. So, a UK phone number would be 02081234123 and an international one would be 001800300300.

You should never store values with format. Formatting should be done in the view depending on user preferences.

Searching for phone nunbers with mixed formatting is near impossible.

For this case I would split into fields and store as integer. Numbers are faster than texts and splitting them and putting index on them makes all kind of queries ran fast.

Leading 0 could be a problem but probably not. In Sweden all area codes start with 0 and that is removed if also a country code is dialed. But the 0 isn't really a part of the number, it's a indicator used to tell that I'm adding an area code. Same for country code, you add 00 to say that you use a county code.

Leading 0 shouldn't be stored, they should be added when needed. Say you store 00 in the database and you use a server that only works with + they you have to replace 00 with + for that application.

So, store numbers as numbers.