6

My database contains a list of phone numbers which is of varchar type. Phone number may be in any of these formats

12323232323
1-232-323 2323
232-323-2323
2323232323

Instead of the – symbol there may be ( ) , . or space And if I search for 12323232323, 1-232-323 2323, 232-323-2323, or 2323232323 it should display all these results. I need to write a query for this.

Kyle Trauberman
  • 25,414
  • 13
  • 85
  • 121
Juice
  • 3,023
  • 6
  • 39
  • 66
  • Have you looked at [MySQL string functions](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html)? – OMG Ponies Nov 09 '11 at 04:37
  • Do you really need to save the formatting of phone numbers? Have you considered storing the phone numbers in a single format and reformatting them when you display them based on the current user's localization settings? –  Nov 09 '11 at 13:49
  • 1
    @Phoenix I agree that you may not want to store the format with the number but I don't think it would make sense to format based on the user's locale say a US number in a German format. If localization was a concern you'd probably need to store the country code and then format the number based on that code. – Conrad Frix Nov 09 '11 at 17:07
  • @ConradFrix interesting observation, and good point. –  Nov 09 '11 at 22:52

3 Answers3

10

I think it is not efficient to do this realtime, I propose two options.

  1. clean the data, so there will be only one format.

  2. add another column which contains the clean data, so when you search, you search for this column, when display you can display the various format data.

James.Xu
  • 8,249
  • 5
  • 25
  • 36
2

I agree with James, but if you really need to search the database as it is, perhaps MySQL's REPLACE operator will get you where you need to go. Something like 

select * from mytable where replace(crazynumber,'-','')='23232323';
fimbaz
  • 61
  • 2
  • it's working fine for single character replace. Is it possible to replace an array of string? In my case there may be `( ) , . or space` Instead of `-`. – Juice Nov 09 '11 at 05:34
  • i tried with array like this `"where replace(c.customers_telephone,'".$chars."','' )='" . $onlynumbers . "'";` and i am getting the query run like this `where replace(c.customers_telephone,'Array','' )='2323232323'` – Juice Nov 09 '11 at 05:37
  • 2
    Note that if you go this route, you can't use an index to speed up your query; you will force MySQL to do a full table scan for EVERY query. I strongly recommend you sanitize your data instead. –  Nov 09 '11 at 13:47
1

How to Replace Multiple Characters in SQL?

Can MySQL replace multiple characters?

Agree with James, but if u really need to do this, the above two links have proposed the prefect solutions for your scenario.

Community
  • 1
  • 1
Zohaib
  • 7,026
  • 3
  • 26
  • 35