I have a mysql table with phone numbers in it, but I can't control the type of data that goes into this field. It may be...
555-555-5555
(555)555-5555 ext 55
Not available at this time
555-555-5555 (don't call during the day)

...They aren't that extreme, but I can't just strip out the non-numeric characters before it gets into the database. (Yuck...I know)

Obviously, I am running into problems with searches. I just need to be able to allow people to search for 5555555555 and have it pull up any records with this number in it.

I have been experimenting with RLIKE/REGEXP, but can't seem to make it do what I want it to do. Any thoughts?

    May i ask why is it that you can't limit the entry to numeric characters only?

      The client wants to be able to enter strings like "See notes". I just double-checked with them this morning and it has to be this way. :-(

        Just a thought maybe you should have two fields to store the info, one as a numeric value where you strip all non-numeric values and the other field will have all characters entered.

          It depends on what you allow them to enter for the search criteria, but if you have them enter a string of digits (or really any other string), just explode the string and join it back together with a [0-9] between each character so it allows any junk except the numbers you are looking for:

          SELECT *
FROM phonenumbers
WHERE description REGEXP '5[^0-9]*5[^0-9]*5[^0-9]*5[^0-9]*5[^0-9]*5[^0-9]*5[^0-9]*'
            8 days later

            Thanks for both of your responses. I really appreciate the help. Since I don't have a quadrillion records, I ended up giving the regex solution a try. I had to find a work-around for not having str_split in our version of PHP, but all is good. The search is working perfectly!

              Write a Reply...