3

I have phone number saved for my clients in my database, As you know the phone number has no specfic format, so some numbers are like (+1)123 3123 OR 212-43-1., problem is with in my php code I make a query to check either a phone number exist so If I enter number like 11233123 then it will result no result but it is already there. Any solution on how to match? The solution in my mind is to remove all the special characters while checking

  • You should format each number with a pattern then save it, and while searching it, validate the format then search. your pattern might be the one that you talked about: removing all special chars. – Jason OOO Feb 06 '14 at 20:40
  • 4
    The solution is to remove anything non-numeric on the way _in_ to the database. You should only be formatting phone numbers when presenting the numbers to a user in the UI. – Sean Bright Feb 06 '14 at 20:40
  • possible duplicate of [Searching for phone numbers in mysql](http://stackoverflow.com/questions/40873/searching-for-phone-numbers-in-mysql) – Michal Brašna Feb 06 '14 at 20:43
  • @Seanbright The problem with normalising in the database is that you then have to format on the way out, and that is itself fraught with difficulties. For that reason I tend to store both forms. –  Feb 06 '14 at 20:49
  • @MikeW that is a good idea – Sean Bright Feb 06 '14 at 20:50
  • @MikeW I haven't messed with phone numbers much. What are the difficulties to removing all non-integer characters from a string? Or am I missing something? – Jasper Feb 06 '14 at 20:53
  • @Jasper because depending on the country the phone number is located in the formatting may be completely different. – Sean Bright Feb 06 '14 at 21:22

2 Answers2

0

You can use REPLACE to remove special characters from the data in the database during your query. Or when you add a phone number, in PHP, remove the special characters and save the phone number as just the numbers. Then search against the column that you removed the special characters from.

Here is documentation for REPLACE (it's under strings, don't get confused with the REPLACE query type): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

As stated by most people posting on this page, cleaning the input before adding it to the database is the preferred method.

Jasper
  • 75,717
  • 14
  • 151
  • 146
0

Phone numbers in database should be for this specific reason normalized.

Or you can take a look at another questions here how to search in chaos.

Community
  • 1
  • 1
Michal Brašna
  • 2,293
  • 13
  • 17