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

long story short, let's assume have field in column called 'whyisthisalist' 'table' contains:

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

how extract text between example1 , example2, given example2 isn't there because this:

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

it should return "hereistext" if goes well.

my idea far:

$sql = "select substring(left(whyisthisalist, locate('\";', whyisthisalist) +0), locate('example1:\"', whyisthisalist) +0, 100) table locate('\";', whyisthisalist) > 0 , locate('example1:\"', whyisthisalist) > 0 order id desc limit 1"; 

what needs done/doesn't work: need next occuring "; after previous located string. possible? or there other workaround? i'd glad help.

the following regex first colon first semi-colon:

\:([^;]*) 

simplifying query this:

select `whyisthisalist` regexp '\:([^;]*)' `table` 

mysql regex docs
mysql pattern matching

you can use lookahead's , lookbehind's regex:

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