|
|
Posted by strawberry on 03/01/07 18:12
On Mar 1, 5:05 pm, "David Smithz" <dave1...@blueyonder.co.uk> 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
>
> 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?
No help really, just a couple of things to be wary of...
In my contacts list, I have mobile numbers as follows:
079*******7/079*******3
+41(0)79*****0
077*******7
30*******5
As far as I know, these are all valid - but I'd be hard pushed to
think of a rule to correctly process all of them. Still, there's
probably someone out there for whom this is easy peasy.
Good luck!
[Back to original message]
|