At 6:15 PM -0500 7/14/01, Paul DuBois wrote:
>At 3:15 PM -0700 7/14/01, xris wrote:
>>I have a very simple query that used to work and then recently stopped. It
>>goes something like:
>>
>>SELECT name FROM Items WHERE category RLIKE '^\* new cat \*';
>>
>>Now, I'm pretty familiar with how regex works, and I was pretty sure that
>>when I put a \ in front of the * it would interpret it as a * instead of the
>>"0 or more of previous" operator. But it's giving me the following error:
>>
>> Got error 'repetition-operator operand invalid' from regexp
>>
>>I really need to figure this out. I was told by someone to double-escape
>>them (like" "^\\* new.."), but to me that would read as "0 or more \
>>characters at the beginning of the string", not "a string that starts with
>>the * character" which is what I need.
>>
>>Help?
>
>Did you try the advice you were given?
>What happened?
I decided to follow my own advice and check it out:
mysql> select '* new cat * ' rlike '^\* new cat \*';
ERROR 1139: Got error 'repetition-operator operand invalid' from regexp
Okay, that matches what you got. Now to try double-\\ with a string
that should match:
mysql> select '* new cat * ' rlike '^\\* new cat \\*';
+-----------------------------------------+
| '* new cat * ' rlike '^\\* new cat \\*' |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)
And with a string that should not:
mysql> select ' * new cat * ' rlike '^\\* new cat \\*';
+------------------------------------------+
| ' * new cat * ' rlike '^\\* new cat \\*' |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row in set (0.00 sec)
Looks like the double-\ is the correct thing.
Your reasoning about why it wouldn't work is correct, except for
one thing: one backslash gets stripped at the lexical level during
the query parsing phase. That leaves the other one to be interpreted
during query execution. That's why \\* and \* doesn't.
--
Paul DuBois, paul@stripped

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.