CODEWITHSUNDEEP
mysqlregex
Insomnia88
Insomnia88

Reputation: 368

How do I match for a missing word in mysql with regex

I am struggling with regular expressions in mysql. Since there is are no positive/negative lookbehinds/aheads how can I search for string that doesn't contain a specific substring? I build a regex that would work fine in PCRE and looks like this

/* example where it should match entry 1:
entry 1: ...lorem ipsum [table mode="foo" offset="1"] dolor sit...
entry 2: ...lorem ipsum dolor sit...
entry 3: ...lorem ipsum [table language="en" mode="foo"] dolor sit...
*/    
'\[table.*(?<=language).*\]'

How can I achieve the same in MYSQL?

Upvotes: 2

Views: 155

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626748

You can use

WHERE col NOT REGEXP '\\[table[^][]*language' AND col LIKE '%[table %'

The NOT REGEXP '\\[table[^][]*language' condition makes sure there is no [table tag with language word inside in the record and the LIKE '%[table %' condition matches a record that contains [table string.

Regex details:

  • \[table - [table
  • [^][]* - zero or more chars other than [ and ]
  • language - a literal string

If you are using MySQL v8+, you can use

WHERE col REGEXP '^(?!.*\\[table\\b[^\\]\\[]*language).*\\[table\\b'

See the regex demo. Note that in ICU regex syntax, both [ and ] are special inside square brackets.

Upvotes: 1

Related Questions