mysqlregexrlike

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'
Solution

  • Use a negated character class and groups that can skip over bracketed input, to require that the target is not with brackets, plus start/end anchors:

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

    See live demo.