mysql regular expression

[lenerd3000]

hello,

 

anyone here knows this problem?

 

i have phone numbers in my database and there's 3 formats

1. (555) 235-6589

2. 125 246 4256

3. 124.265.1545

 

my problem is how can i compare my input from my phone number. i need a unique phone number in my database. what if the inputted format is 265 568 5659 how can i compare it in a phone number with this format 265.568.5659 or (265) 568-5659. i know how to get numbers in the inputted data but i dont know how to get numbers from the database's phone numbers so that the comparing of the two data will be all number like this 2655865659 = 2655865659.... any good idea for this problem? i appreciate it most.

[effigy]

I would put the phone numbers into a standard format before they are inserted into the database. You could clean these fields up using REPLACE.

[lenerd3000]

sounds like a good idea.. but i cant change now a thousand records of phone number for the standardization. i have already inserted it in my database... any other idea... the website is already online. i am trying to make a good regex for that.

[effigy]

It's never too late to clean things up You're essentially going to be doing the same process, the only difference is whether the database itself is modified or not.

 

Perhaps something like:

 

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), '.', ''), ' ', '') AS phone;

[fenway]

You should standardize it right now...

[lenerd3000]

thanks for the advice. ill try to standardize the phone numbers now. but i have data already on my database.  :'(

[lenerd3000]

this is my expression:

 

select '858-659-5595' REGEXP '?[\(]858?[\)]?[\-\. ]659?[\-\. ]5595'

 

but it produce this error:

 

#1139 - Got error 'repetition-operator operand invalid' from regexp

 

my only problem is how can i tell the regexp the parenthesis () is optional. the rest is ok only the parenthesis part is not.

[effigy]

Try \(?858\)?[-. ]?659[-. ]?5595