1

Regular regex:

foo(\((\d{1}|\d{2}|\d{3})\))?

This regex works in Java:

foo(\\((\\d{1}|\\d{2}|\\d{3})\\))?

Examples:

fooa      //no match
foo(1)a   //no match
foo(a)    //no match
foo(1)    //match
foo(999)  //match
foo       //match

MySQL 5.5 documentation (https://dev.mysql.com/doc/refman/5.5/en/regexp.html) says

Note:
Because MySQL uses the C escape syntax in strings (for example, “\n” to
represent the newline character), you must double any “\” that you use 
in your REGEXP strings.

I tried as a test running the following on MySQL 5.x

select 'foo' REGEXP 'foo(\\((\\d{1}|\\d{2}|\\d{3})\\))?'

Here is the error message I get:

Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ''foo(\\([(]\\d{1}' at line 1

I looked at Adapting a Regex to work with MySQL and tried the suggestion of replacing \d{1} etc.. with [0-9] which gave me:

select 'foo' REGEXP 'foo(\\(([0-9]|[0-9]|[0-9])\\))?'

But still getting MySQL death.

Community
  • 1
  • 1
Harrison F
  • 492
  • 5
  • 15
  • 2
    I just copied your SQL syntax and dropped on my MySQL GUI and worked neat. By the way, it returns 1. – Luiggi Mendoza May 18 '13 at 00:10
  • 1
    What MySQL version is running on your server? – Jan Dörrenhaus May 18 '13 at 00:11
  • @LuiggiMendoza hrmm wacked. I was testing it in SQuirreL and it dies there. I dropped it into MySQL Workbench and it works. That's so bizarre. Must be a SQuirreL bug or something. Thanks! haha – Harrison F May 18 '13 at 00:15
  • Any way to mark this as complete / answered / not a problem? – Harrison F May 18 '13 at 00:16
  • 2
    I think the best thing to do would be for you (or someone) to find out exactly why it is failing in SQuirreL and then post that as an answer – CrayonViolent May 18 '13 at 00:18
  • So do you really need it for SQuirreL or for MySQL? – CrayonViolent May 18 '13 at 00:27
  • @CrayonViolent MySQL. I just use SQuirreL for all my testing / queries. The auto-complete is very nice, and I like a lot of the features over MySQL Workbench. Although this bug is disconcerting. If I get some time this week I look into this problem and get back. – Harrison F May 20 '13 at 16:18

2 Answers2

1

Not having an immediately availble MySQL console to verify, this should work:

'foo\\([:digit:]{1,3})\\)?'

Your other regexes have capture groups around both foo(123) and foo(123). It doesn't look like you want the capture groups in MySQL (does it even support them?), which would lead to MySQL choking.

someone52746518
  • 51
  • 2
1

Popping in because I ran into this and found the problem/solution.

Go go Global Preferences -> MySQL tab. Under "Use Custom Query Tokenizer" there is a "Procedure/Function Separator." If that is "|" change it to something else (like "/"). This is what's causing SQuirreL to fail parsing the REGEX.

David Rosenfield
  • 11
  • 1