Mysql error " repetition-operator operand invalid"

Question

I'm getting a strange mysql error that I've never really seen before. It's caused by a query similar to the one below. Any advice?

SELECT *
  FROM table
 WHERE col REGEXP '^* MAN *$'  
 LIMIT 100

Error:

#1139 - Got error 'repetition-operator operand invalid' from regexp

-- UPDATE --

Ok I can confirm that the following two solutions worked. Thanks to the help of both answers below:

SELECT *
  FROM table
 WHERE col REGEXP '^(\\*) MAN (\\*)$'  
 LIMIT 100

SELECT *
  FROM table
 WHERE col REGEXP '^[*] MAN [*]$'  
 LIMIT 100

Possible duplicate of [#1139 - Got error 'repetition-operator operand invalid' from regexp](https://stackoverflow.com/questions/18317183/1139-got-error-repetition-operator-operand-invalid-from-regexp) — Jens, Dec 07 '18 at 09:05
What is the purpose of `^*`, Did you mean `^.*` because the first one reads to me as one or more start of line — Joakim Danielson, Dec 07 '18 at 09:09
@nikhilsugandh I'm trying to match the string "* MAN *" as an exact match But to cut a log story short i need to use regex. And im needing something like "^\* MAN \*$". But thats the error I get above regardless of escaping — TheMan68, Dec 07 '18 at 09:24
@Jens yeah I looked into this question yesterday but didn't seem to be that helpful for me. I need to use regex. It can't be a like. It's a long story but regex is the most reliable results we've had in our use case. But just incase the user puts in these type of chars I need to find out how to do this. — TheMan68, Dec 07 '18 at 09:27
Have you tried `WHERE col REGEXP '.* MAN .*' ` or `WHERE col REGEXP '^.* MAN .*$'`? — Joakim Danielson, Dec 07 '18 at 09:36
What is the reason for using such regular expression? Give data example. — Kedar Limaye, Dec 07 '18 at 09:42
@JoakimDanielson hi there. the * need to be treated as a char not regex. like \* but i still get the same problems — TheMan68, Dec 07 '18 at 09:50
`'^\* MAN \*$'` for a literal string, actually resulting in **equality**: `col = '* MAN *'` (apart from case insensitive comparison). — Joop Eggen, Dec 07 '18 at 10:03

Nikhil S · Accepted Answer · 2018-12-07T09:50:45.910

3

\ and * itself are metacharacters thats why * is to be followed by two \ so that seconod \ is also not considered as a a literal but metacharactcer

this will work:

SELECT *
FROM Table1
WHERE column1 RLIKE '(\\*)(man)(\\*)';

see link its working now:http://sqlfiddle.com/#!9/e695cf/1/0

edited Dec 07 '18 at 09:50

answered Dec 07 '18 at 09:10

Nikhil S

3,786
4
18
32

you have to use escape character to amtch it wiht * like this: `WHERE REGEXP_LIKE(col,'^(\*)(MAN){1}(\*)$')` – Nikhil S Dec 07 '18 at 09:27
I tried "WHERE col REGEXP '^(\*)(MAN)(\*)$' " and I get the following error " Got error 'repetition-operator operand invalid' from regexp" – TheMan68 Dec 07 '18 at 09:30
Yeah I got this error " FUNCTION database.REGEXP_LIKE does not exist" – TheMan68 Dec 07 '18 at 09:34
just got this error: "Got error 'repetition-operator operand invalid' from regexp" – TheMan68 Dec 07 '18 at 09:43
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184865/discussion-between-nikhil-sugandh-and-theman68). – Nikhil S Dec 07 '18 at 09:54
Hi great. Yeah that doesn't break the query but it also doesn't find any results when there is one in the db – TheMan68 Dec 07 '18 at 10:07
1

Ah my bad. I had spaces but there were no spaces in the regex. it works. Thank you – TheMan68 Dec 07 '18 at 10:16

score 1 · Answer 2 · answered Dec 07 '18 at 10:13

This should work if you want to match 'bla bla * MAN *bla bla' for instance

SELECT *
FROM table
WHERE col REGEXP '[*] MAN [*]'

If you want to match '* MAN *'

SELECT *
FROM table
WHERE col REGEXP '^[*] MAN [*]$'

or without regexp

SELECT *
FROM table
WHERE col ='* MAN *'

Mysql error " repetition-operator operand invalid"

2 Answers2