Need REGEXP Matching in MySQL

Question

I want to match a String which looks like this:

[lang_de]Hallo![/lang_de][lang_en]Hello![/lang_en]HeyHo[lang_es]Hola![/lang_es]

I want the matching to return true if there is text which is not enclosed by lang_tags (in this example: HeyHo). It could also be positioned at the beginning or end of the string.

Whitespace should NOT match, e.g. [lang_de]Tisch[/lang_de] [lang_en]Table[/lang_en] should not cause a match.

I can't use lookahead or lookback, because MySQL doesnt seem to support this.

Any Suggestions?

Answer 1

Try this regex:

'^ *[^[ ]|\\[/lang_[a-z]{2}\\] *[^[ ]'

This is how you can use it:

select * 
from <table> 
where <field> regexp '^ *[^[ ]|\\[/lang_[a-z]{2}\\] *[^[ ]'

It should handle all cases:

• Before
• After
• Middle
• Not whitespace

Answer 2

my best attempt is:

SELECT * FROM tbl1 WHERE ('[lang_de]Hallo![/lang_de][lang_en]Hello![/lang_en]HeyHo[lang_es]Hola![/lang_es]' NOT REGEXP '^(\\[lang_[a-z]{2}\\][^\[]*\\[\\/lang_[a-z]{2}\\])*$')

Let us know how you got on.