Searching Phone Numbers in MySQL

My column in MySQL to store phone numbers is varchar so the phone numbers can be entered different formats e.g. 1112223333, 111-2223333,111-222-3333, (111)222-3333 etc. I need to find the way to search a phone number in this column assuming that the user can enter the phone number (s)he wants to search for in different formats as well. So for example if the user searches for 111-2223333 the search should return the row with the entry: (111)222-3333.
Any help with be appreciated.

You could use something like this

SELECT * FROM phones WHERE phone IN ('111-2223333', '(111)222-3333')

but I would normalize the phone data (eg. 1112223333) before inserting them in database. You could also separate the country/area code from the phone number.

Btw, this is a db question.

The formats I showed were just examples. There could be more… Looking for more generic approach :slight_smile:

If you have to keep pre-formatted phone numbers, then continue to do so, but create an extra row with numbers only.

id | 23
friendly_phone_number | (111) 222-3333
phone_number_only | 1112223333

Then when someone searches for “111 222-3333” then reduce that down and look for a match in the phone_number_only column.

You’d have PHP replace the non-numerical characters.

Either that or investigate the results that having a mysql FULLTEXT index offers you.

A generic approach would be the normalization of the phone data, before inserting them in database.

If you strip everything except digits, you can store just the digits, and do the same for your search.

If you want to have a formatted display, there is a lot of code available that will display the numbers in the format you prefer. I did a Google search and found several different ways of displaying the phone numbers from my database, and those were just the USA formats.

Thank you Cups. I like your approach with an additional column storing digits only. So far it looks like the best solution which allows to preserve phone numbers formats entered by users.

Thanks, its more or less what everyone else is saying - but acknowledges that users might enter numbers into your system in a way that suits them and makes it easier for them to read.

It does face you with extra complexity, I admit, the main thing not to overlook is “what if someone edits their phone number?”

It should be fine. I will just make sure that both fields are being updated.