Aza Aza - 7 months ago 109
SQL Question

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.

Answer

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'