whitesiroi whitesiroi - 6 months ago 8
MySQL Question

How to get all matching words except those in parentheses? Regex/Mysql

Here is the example I have:

JELLY2some text

some text{JELLY2}some textsome textsome text
Sample text for testing:
some textJELLY2 {some text JELLY2 lsdkfjsd}„Āęsome text


I want to get all
JELLY2
except those in parentheses like:

{JELLY2}


and

{some text JELLY2 lsdkfjsd}


http://regexr.com/3dhsl

I need to get data by select statement, something like:

SELECT `id` FROM `table` WHERE `body` REGEXP 'JELLY2'


Or maybe, if it's possible with RLIKE or some other way?

SELECT `id` FROM `table` WHERE `body` RLIKE 'JELLY2'

Answer

Use a negated character class and start/end anchors:

SELECT id
FROM table
WHERE body RLIKE '^([^{]*(\{[^}]*\})?)*JELLY2([^{]*(\{[^}]*\})?)*$'

See live demo.