Home > Archive > PHP SQL > March 2007 > Regular expression in MySQL (need to strip number fiel of spaces and then match).
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Regular expression in MySQL (need to strip number fiel of spaces and then match).
|
|
| David Smithz 2007-03-01, 7:04 pm |
| 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?
| |
| strawberry 2007-03-01, 7:04 pm |
| 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:
>
>
> 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!
| |
| David Smithz 2007-03-01, 7:04 pm |
| Ideally another way of doing this would be to use the regular expression in
a replace statement (currenlty playing around with this).
e.g.
SELECT REPLACE (mobile_number, ' ', '') FROM `mytable` WHERE 1 LIMIT 5
the above works in removing the blank spaces, but if I could instead have a
regular expression to say, instead of just spaces, any non numercial
character, this would be good.
| |
| Dave Fountain 2007-03-04, 3:59 am |
| On Thu, 01 Mar 2007 18:32:16 GMT, "David Smithz"
<dave1900@blueyonder.co.uk> wrote:
>Ideally another way of doing this would be to use the regular expression in
>a replace statement (currenlty playing around with this).
>
>e.g.
>
>SELECT REPLACE (mobile_number, ' ', '') FROM `mytable` WHERE 1 LIMIT 5
>
>the above works in removing the blank spaces, but if I could instead have a
>regular expression to say, instead of just spaces, any non numercial
>character, this would be good.
>
I know nothing about the topic but is there a function that allows you
to *keep* numerical characters, then you would only have to check for
10 cases?
| |
| Dave Fountain 2007-03-04, 3:59 am |
| On Sun, 04 Mar 2007 09:36:52 +0000, Dave Fountain
<daveatdaveandsalcouk> wrote:
>On Thu, 01 Mar 2007 18:32:16 GMT, "David Smithz"
><dave1900@blueyonder.co.uk> wrote:
>
>I know nothing about the topic but is there a function that allows you
>to *keep* numerical characters, then you would only have to check for
>10 cases?
Scrap that idea - I saw you could have non-telephone numbers in the
field, but you could also have problems if they wrote something like:
ring after 9 07777 33333 if you just remove the spaces.
|
|
|
|
|