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?

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

One would hope you're sanatizing your input anyway, seeing as it's coming from an unknown source...

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

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

oh bike, you are a kidder