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 '\\[([^\\]]+)?<([^\\]]+)?>\\]'

Quassnoi · Accepted Answer · 2009-06-05 17:37:32Z

6

This works for me:

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

answered Jun 5, 2009 at 17:37

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

steamer25 · Accepted Answer · 2009-06-05 17:33:40Z

0

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)

edited Jun 5, 2009 at 17:33

answered Jun 5, 2009 at 17:25

steamer25

9,6731 gold badge35 silver badges38 bronze badges

Collectives™ on Stack Overflow

mySQL Regexp with square brackets

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related