0

What is the regular expression to match a phone number inside a TEXT field in a table? I am trying to match mainly US numbers.

What I have found so far is this:

"07[^0-9]*123[^0-9]*456"

But this one tries to match ONLY phone numbers. What I am trying to do is match TEXT fields that contain phone numbers.

This is my query:

select count(*) as phone_number from myTable 
where Query REGEXP "07[^0-9]*123[^0-9]*456";

Thank you in advance.

sokras
  • 629
  • 1
  • 8
  • 19
  • 2
    Provide examples of phone numbers that you want to match, and perhaps even the text fields that they might be in. – Gordon Linoff Nov 26 '14 at 17:02
  • Define "phone number". If you can you're probably doing it wrong. The best you can hope for is *probable* phone numbers. Is `1.877.888.8888` a valid number? How about `1(900) 222-3333 x 2400`? – tadman Nov 26 '14 at 17:02
  • 2
    possible duplicate of [A comprehensive regex for phone number validation](http://stackoverflow.com/questions/123559/a-comprehensive-regex-for-phone-number-validation) – Devon Bessemer Nov 26 '14 at 17:03
  • @tadman yes these numbers are valid. as long as the string contains any kind of phone numbers. the rest of the string can contain anything – sokras Nov 26 '14 at 17:05
  • Are you comparing with `REGEXP`? Then text **containing** the pattern you're looking for should be returned just fine, unless you explicitly add `^` and `$` at the beginning and end of your pattern ... – Hartmut Holzgraefe Nov 26 '14 at 17:22
  • Seeing the exact full `SELECT` statement you're using may clarify things a bit – Hartmut Holzgraefe Nov 26 '14 at 17:22
  • just added my query to the question – sokras Nov 26 '14 at 17:24

0 Answers0