Uncooperative REGEXP 1

[OsakaWebbie]

I have a REGEXP expression I have studied and studied, but it doesn't work for some reason in MySQL. I tested the same expression in PHP (just as another handy language that can process regular expressions) and it works there. So there is apparently either something special about MySQL's implementation of regular expressions, or some of the special characters are being interpreted differently - either by MySQL directly or by something along the way in the OS, since I am not able to log into the MySQL command line and test it that way.

I will break it down to a real simple example (if you want more background, read the last part of thread thread436-1216320, starting where I say I'm revisiting with a related question). If I run this query, I get 0 (i.e. no match) in MySQL, but the same thing in PHP is a match:

SELECT 'My A[X]BCs' REGEXP '(\[X\])?A(\[X\])?B(\[X\])?C'

If I remove the [X] and just search in "My ABCs", it matches, but the whole point of the query is to find data that contains a string (in this example, "ABC") even if there are things in brackets interspersed in unknown places in the data.

This is very simplified from what I really need to do (for example, this assumes the contents of the bracket will always be X, when in reality I allow anything), but even this simple test fails. Does anyone have any thoughts about what might be wrong?

 

[TonyGroves]

You need to double your backslashes, so that they are passed literally to the parser:
[tt]
SELECT 'My A[X]BCs' REGEXP '(\\[X\\])?A(\\[X\\])?B(\\[X\\])?C'
[/tt]

 

[OsakaWebbie]

Hooray! I finally made it simple enough that you understood what I was driving at, and then, as is befitting the #1 MVP on the MySQL forum, you immediately saw the problem. Yes, that solved it, although the end result in my code looks pretty strange, because it turns out that during the replace operation I'm using to put the expression between each of the characters, doubles somehow turn back into singles, so I ended up needing to start out with four backslashes in order for MySQL to end up with one! But it works now. Thanks a bunch!