mySQL Regexp with square brackets

Question

I am trying to match strings like '[sometext<someothertext>]' (i.e., left square bracket, text, left angle bracket, text, right angle bracket, right square bracket) within a column in mySQL. Originally I used the following query (notice that since regex queries are escaped twice in mySQL, you must use two backslashes where you would normally use one):

SELECT * FROM message WHERE msgtext REGEXP '\\[(.+)?<(.+)?>\\]'

This query received no errors, but it returned things I didn't want. Instead of the (.+), I wanted [^\]] (match everything except a right square bracket). When I changed the query, I got the following error: "Got error 'repetition-operator operand invalid' from regexp"

After reading through the mySQL documentation here, it states "To include a literal ] character, it must immediately follow the opening bracket [." Since I want "^\]" instead of "]", is this even possible since the bracket can't be the first character after the opening bracket? Below are some of the queries I have tried which get the same error listed above:

SELECT * FROM message WHERE msgtext REGEXP '\\[([^\\]]+?)<([^\\]]+?)>\\]'
SELECT * FROM message WHERE msgtext REGEXP '\\[[^\\]]+?<[^\\]]+?>\\]'
SELECT * FROM message WHERE msgtext REGEXP '\\[[^[.right-square-bracket.]]]+?<[^[.right-square-bracket.]]]+?>\\]'

UPDATE:

The following query runs without errors, but does not return any rows even though I know there are columns which match what I am looking for (based on my original query at the top):

SELECT * FROM message WHERE msgtext REGEXP '\\[([^\\]]+)?<([^\\]]+)?>\\]'

Answer 1

Your final regex looks correct and works in Firefox/JS once the slashes are unescaped. Doesn't look like MySQL supports capture groups natively though... Maybe that's the problem.

Perhaps this would useful: http://mysqludf.com/lib_mysqludf_preg/

Also, you might try a * instead of +? for your negated right squares.

* means 0 or more repetitions (greedy)
+? means 1 or more repetitions (lazy)

Answer 2

This works for me:

SELECT '[sometext<someothertext>]' REGEXP '\\[([^[.right-square-bracket.]]+)?<([^[.right-square-bracket.]]+)?>\\]$';