How to store phone numbers on MySQL databases

Question

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

User · Accepted Answer

All as varchar  they aren t numbers but  collections of digits   Country   area   number separately Not all countries have area code  eg Malta where I am  Some countries drop the leading zero from the area code when dialling internal  eg UK  Format in the client code

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Form my point of view  below is my suggestions    Store phone number into a single field as varchar and if you need to split then after retrieve split accordingly  If you store as number then preceding 0 will truncate  so always store as varchar Validate users phone number before inserting into your table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to string

Examples related to database-design

Examples related to formatting

Examples related to int