List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 14 2001 11:29pm
Subject:Re: Escaping * character in a RLIKE? How? Help?
View as plain text  
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
Thread
Escaping * character in a RLIKE? How? Help?xris15 Jul
  • Re: Escaping * character in a RLIKE? How? Help?Paul DuBois15 Jul
    • Re: Escaping * character in a RLIKE? How? Help?Paul DuBois15 Jul