[thelist] Regular Expression help (MySQL)

[Rick den Haan]

Hello List,

 

I need to run a REGEXP LIKE sort of construction on a MySQL-statement.

 

Modifying the database structure is (unfortunately) not an option (lack of
permissions), and unfortunately, whoever designed the database thought it
was a good idea to store phone numbers as a VARCHAR, and building the script
to allow spaces, dashes, brackets and whatnot. So I can have a phone number
formatted as 123456789, 123-456 789, (123) 456-789, or any combination
thereof.

 

Now I need the ability to search by phone number.

 

What I need, is the stored phone number, with any non-numeric character
stripped out. I'd do that in PHP by throwing a [^0-9] regular expression at
it, but it looks like MySQL doesn't support regular expressions in its
REPLACE() function.

 

Here's what I'd like to do, simplified. Can anyone think of a way to
actually make it work?

 

SELECT `id_customer`

FROM tbl_customer

WHERE REGEXP_REPLACE(`phone`, '[^0-9] ', '') LIKE '%678%'

 

Google just informed me that Oracle seems to come with a REGEXP_REPLACE
function. Any way to mimic it in MySQL?

 

In case the solution requires a recent MySQL-version, the server is running
version 5.0.51a.

 

Thanks in advance for any help you might be able to provide.

 

Rick.