Extracting MySQL data within "tags" using regular expressions? [duplicate]

This question already has an answer here: Closed 11 years ago.

Possible Duplicate:

Simulating regex capture groups in mysql

Good day,

I have many rows of data stored开发者_JAVA百科 in a MySQL table. A typical value could look something like this:

::image-gallery::
::gallery-entry::images/01.jpg::/gallery-entry::
::/image-gallery::

Is there a way - by means of a regular expression that I can a) extract the term image gallery from the first line (it could be any phrase, not just image-gallery) and then extract the center line as two separate values like this:

gallery-entry and then images/01.jpg

There could be many lines of ::gallery-entry:: values, and they could be called anything as well. A more complete example would be:

::image-gallery::
::title::MY GALLERY::/title::
::date::2011-05-20::/date::
::gallery-entry::images/01.jpg::/gallery-entry::
::/image-gallery::

In essence I want this information: The content type (image-gallery) in the above case, first line and last line. Then I need the title as a key value style pair, so title as the key and MY GALLERY as the value. Then, subsequently, I would need all the rows of fields thereafter (gallery-entry) as key value pairs too.

This is for a migration script where data from an old system will be migrated over to a new system with different syntax.

If MySQL select statements would not work, would it be easier to parse the results with a PHP script for data extraction?

Any and all help is always appreciated.

Kind regards, Simon

---

Try this regex:

::image-gallery::\s+::title::(.*?)::/title::.*?::gallery-entry::(.*?)::/gallery-entry::\s+::/image-gallery::

1. Use single-line mode (/pattern/s) so the .*? chews up newlines.
2. Your key-value pairs will be: title: $1 (matching group 1) gallery-entry: $2 (matching group 2)

---

From simulating-regex-capture-groups-in-mysql there does not seem to be a way to easily capture groups with a regex in mysql. The reason is that MySQL does not natively support capture groups in a regex. If you want that functionality you can use a server side extension like lib_mysqludf_preg to add that capability to MySQL.

The easiest way is to extract the whole column with SQL and then do the text matching in another language (such as php).

In my tests kenbritton's regex didn't work, but building off of it the following regex worked on your test data:

::image-gallery::\s+::title::(.*?)::\/title::\s+(?:.*\s+)*::gallery-entry::(.*?)::\/gallery-entry::\s+::\/image-gallery::