I have strings containing sequences like these:

^^fruit/citrus^fruit/citrus/orange^meat^vegetables/carrot^^
^^fruit/citrus^vegetables^vegetables/carrot^^
^^fruit/citrus/orange^vegetables/carrot^^
^^fruit/citrus/lemon^vegetables/potatoe^^

Steps in a sequence are separated with ^ character.

I would like to be able to use a multi-character wildcard to search those strings, but the wildcard should work only within a scope of a step. For example this pseudo-query:

^^[fruit/*]^vegetables/*^^

Should match all seqences starting with any step starting with "fruit/" followed by a step starting with "vegetables/". So these should be matched:

^^fruit/citrus/orange^vegetables/carrot^^
^^fruit/citrus/orange^vegetables/potatoe^^

but not these:

^^fruit/citrus^fruit/citrus/orange^meat^vegetables/carrot^^
^^fruit/citrus^vegetables^vegetables/carrot^^

Is there a way to this in MySQL (5.6 or 8.0) REGEX? Where do I get started?

CodePudding user response：

You can use the regex like

^\^{2}fruit/[^^]*\^vegetables/

See the regex demo. Details:

• ^ - start of string
• \^{2} - ^^ substring
• fruit/ - fixed text
• [^^]* - zero or more chars other than ^
• \^vegetables/ - a ^vegetables/ string.

In MySQL, remember to use double backslashes in regex escapes ("^\\^{2}fruit/[^^]*\\^vegetables/"). Something like SELECT col from table1 WHERE col REGEXP "^\\^{2}fruit/[^^]*\\^vegetables/".