[thelist] Regular Expression help (MySQL)

Rick den Haan rick.denhaan at gmail.com
Fri Jul 4 08:39:21 CDT 2008

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


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.



More information about the thelist mailing list