How to store phone numbers on MySQL databases?

mysql string database-design formatting int
StackOverflowNewbie picture StackOverflowNewbie · Nov 27, 2011 · Viewed 106.1k times · Source

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?

Answer

gbn picture gbn · Nov 27, 2011
  • 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

Related questions

how to convert a string to date in mysql?

I have a string column which acts as a date and I want to select it as a date. Is it possible? My sample data format would be; month/day/year -> 12/31/2011

Read More
How to use GROUP BY to concatenate strings in MySQL?

Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C

Read More
How to escape single quotes in MySQL

How do I insert a value in MySQL that consist of single or double quotes. i.e This is Ashok's Pen. The single quote will create problems. There might be other escape characters. How do you insert the data properly?

Read More