mysql : how to extract a part of field using regex?

Question

In my mysql my address field has data such as:

"5 rue de la Paix, 75000 Paris"

I need to extract the postal code (5 digits always)

\d{5}

and put it into the 'cp' field

Is such thing possible in mysql or must I do a php function for that ?

update data set cp='...

Addresses are not really things that can be parsed with a singular regular expression. You'll probably need dozens if not hundreds to handle anything more than a small subset of them. It's also something you'll want to do with your application layer, not purely in the database, so you have more tools available, like an address parsing library for example. — tadman, Jan 08 '17 at 23:06
http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql you need to create a user defined function — barudo, Jan 08 '17 at 23:07

score 0 · Answer 1 · answered Jan 08 '17 at 23:14

I think you need to use PHP for that:

preg_match("/[0-9]{5}/", "5 rue de la Paix, 75000 Paris",$matches);
echo $matches[0];

/[0-9]{5}/ this searches 5 numbers in the string

$matches this is an array that stores the coincidences

et voilá!

PD: as far as I know In MySql you can do de match, but not extract de "piece" with REGEXP.

1 Answers1