Regular expression in MySQL (need to strip number fiel of spaces and then match).

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?

Re: Regular expression in MySQL (need to strip number fiel of spacesand then match).

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-fun ctions.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

Re: Regular expression in MySQL (need to strip number fiel of spaces and then match).

On Mar 1, 5:05 pm, "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
>
> 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!

Re: Regular expression in MySQL (need to strip number fiel of spaces and then match).

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.

Re: Regular expression in MySQL (need to strip number fiel of spaces and then match).

On Thu, 01 Mar 2007 18:32:16 GMT, "David Smithz"
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?

Re: Regular expression in MySQL (need to strip number fiel of spaces and then match).

On Sun, 04 Mar 2007 09:36:52 +0000, Dave Fountain
wrote:

>On Thu, 01 Mar 2007 18:32:16 GMT, "David Smithz"
> 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?
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.