CODEWITHSUNDEEP
mysqlsqlregex
Aza
Aza

Reputation: 13

MySQL RegExp Got error 'repetition-operator operand invalid'

I'm trying to make a consult in MySQL of telephone numbers, that don't start with '33' and 10 characters long, I try with:

^(?!33)[0-9]{10}$

This is my query:

SELECT calldate, src, dst, billsec, disposition, accountcode FROM cdr WHERE (calldate BETWEEN @inicio AND @fin) AND CHAR_LENGTH(src) = 4 AND disposition LIKE 'ANSWERED' AND billsec >= 1 AND  dst RLIKE '^(?!33)[0-9]{10}$';

But don't work on MySQL, but work in sublime text 3. I got the error:

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp

How can I use a regex in MySQL that macht with that pattern.

Upvotes: 1

Views: 2714

Answers (2)

Scott Weaver
Scott Weaver

Reputation: 7361

from the docs:

MySQL uses Henry Spencer's implementation of regular expressions, which is aimed at conformance with POSIX 1003.2. MySQL uses the extended version to support pattern-matching operations performed with the REGEXP operator in SQL statements.

Lookaheads aren't available with POSIX regex. The best solution may be to put another clause in the select statement:

SELECT  calldate, src, dst, billsec, disposition, accountcode
    FROM  cdr
    WHERE  (calldate BETWEEN @inicio AND @fin)
      AND  CHAR_LENGTH(src) = 4
      AND  disposition LIKE 'ANSWERED'
      AND  billsec >= 1
      AND  dst RLIKE '^[0-9]{10}$'
      AND  dst NOT RLIKE '^33'

Upvotes: 2

Laurel
Laurel

Reputation: 6173

You should remove the negative lookahead: ^[0-9]{10}$

Just use another statement to avoid things that start with 33: AND dst NOT LIKE '33%'.

It's probably faster to do it like this, too.

Upvotes: 0

Related Questions