Posted by J.O. Aho on 03/01/07 17:50

David Smithz wrote:
> Hi there,
>
> I want to achieve the following. I have a column on a table which is a
> mobile number field, however it was entered as a free text field (therefore
> staff might have entered "07222, 222222 - don't call after 9pm" into the
> mobile (cell phone)field).
>
> However I now need to search on that field and this is one way I was
> thinking of doing it
>
> Do a select * from DBTable to get all the fields
>
> Check each field in PHP code and use a regular expression to eliminate all
> the non numerical characters from the field to ensure we can actually do
> like for like tests.
>
> Any matching results store in an array to be used in our application.
>
> But it struck me that rather then bring all the rows from the DB to PHP, it
> would be better if I could do the REGEXP in MySQL directly. I have read up
> on REGEXP but I am not sure if I can do a replace with regular expressions
> in MySQL.
>
> To explain more, in my php code I used the following to tidy up any mobile
> number fields:
>> return trim(preg_replace('/\D+/', '', $mobile_number));
>
> Now it would be great if I could do this on my mySQL query directly. E.g.
>
> Select * from myTable
> where
> mobile_number REGEXP " '/\D+/', '[NO CHARACTERS OR SQUARE BRACKETS HERE]'
> " = SEARCHTERM

Looking at the MySQL on-line documentation, it seems you are close to how it
works, I would guess something like this would work:

WHERE (mobile_number REGEXP '/\D+/') as NewColumn AND NewColumn=SEARCHTERM

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Never used regexp directly in MySQL, so I'm not completely sure about the
syntax or if you need to name the column at all.

--

//Aho

[Back to original message]