2

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.

Deniz Gelion
  • 31
  • 5

1 Answers1

2

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:)(.+?)(?=\;)
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119