在两个字符串之间的列中找到特定字符串并不总是存在？

Question

Long story short, let's assume I have a field in a column called 'whyisthisalist' from 'table' which contains:

{example1:"hereistext";example2:"ohlookmoretext";example3:"isthisevenreal"}

How would I extract the text between example1 and example2, given that example2 isn't always there because it could also look like this:

{example1:"hereistext";example7:"ohlookmoretext"}

It should return "hereistext" if everything goes well.

My idea so far:

$sql = "SELECT SUBSTRING(LEFT(whyisthisalist, LOCATE('\";', whyisthisalist) +0), LOCATE('example1:\"', whyisthisalist) +0, 100)
FROM table
WHERE LOCATE('\";', whyisthisalist) > 0
AND LOCATE('example1:\"', whyisthisalist) > 0 ORDER BY id DESC LIMIT 1";

What needs to be done/doesn't work: I need to get the NEXT occuring "; AFTER the previous located string. Is this even possible? Or is there any other workaround? I'd be glad about some help.

Answer 1

The following regex will get you everything from the first colon to the first semi-colon:

\:([^;]*)

Simplifying your query like this:

SELECT `whyisthisalist` REGEXP '\:([^;]*)'
FROM `table`

MySQL REGEX docs
MySQL Pattern Matching

You can also use lookahead's and lookbehind's for the regex:

(?<=example1:)(.+?)(?=\;)