Search code examples
phpmysqlcontao

Locating a specific string in a column between two strings which are not always there?

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.

Solution

  • 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:)(.+?)(?=\;)