Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: phone number search, strip numbers from string?

    I need to query a string for phone numbers. The phone numbers are in the database as string with optional formating based on location. I need to be able to query against them matching only the numbers. How can I strip the numbers out of a string. I was thinking about using a regexp regular expression, I don't see if/how mysql will return the results of the regex to match on. It seems that it just returns true or false if it matches, which makes the comparison hard since I'd need to format the expression to match the formating of the string I was trying to match against, which may be unknown.

    What I need is for 8455552 to match '(845) 555- 2568' or '845-555-2568' or '(845)5552568' or any variation thereof. so if I can strip the numbers from the string as a string of numbers I can just do myExtractedString LIKE '8455552%'

    so, how can I do it?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ... where replace(replace(replace(replace(phone,'(',''),')',''),'-',''),' ','')
                 like '8455552%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Also remember that your query is unlikely to use any index on the phone number field as you're applying a function to the field first. If it's essential that this is quick then it may be worth automatically storing the telephone number as a string of just digits in another field (perhaps using a trigger) and then adding an index on this field. You could then do faster searches on this new field.

    I usually remember peoples names but forget their phone numbers so I'd want to search by name - why would you want to search against telephone numbers?

    Mike

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    One would hope you're sanatizing your input anyway, seeing as it's coming from an unknown source...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    why would you want to search against telephone numbers?
    because i know a guy whose phone number has 0937 at the end, but i can't remember the first part
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by mike_bike_kite
    I usually remember peoples names but forget their phone numbers so I'd want to search by name - why would you want to search against telephone numbers?
    Originally Posted by r937
    because i know a guy whose phone number has 0937 at the end, but i can't remember the first part
    You can't remember his name?
    What are you going to say when he picks up the phone?
    Hi %0937

    It would be far politer to greet him
    Hi r937
    Mike

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    It also would be of use when checking for duplicate numbers in a contacts table, or if you were operating a call centre / sales function that wanted to target a specific area / STD code.

    personally I quit often can remember some or all of the contacts number (eg they live in / trade out of central Manchester (226) then I need to find those starting +44161236, 0161236 or even 236). IF my app captured the data Id expect all numbers to be +44-161-236 but if you are taking on legacy data or form another source then you may not have that level of control on the original data)

    hey its not 'our' application, who knows the real reason behind the question, it is after all sammydafish's application
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    You can't remember his name?
    oh bike, you are a kidder
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •