Posted by David Smithz on 03/01/07 17:05

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

Therefore, I perform the operation on the mobile_number field directly on
the MySQL data so that it only returns the relevant results. I have in
advance already striped the mobile_number field of the non numerical
characters.

There may be an alternative way of going about this but I cannot see it yet.
Any help?

[Back to original message]