0

why does the following regex give me the infamous 1139 error?

IF NEW.`Email` REGEXP "/^([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)$/i" THEN
    SIGNAL SQLSTATE '10002'
    SET MESSAGE_TEXT = 'Invalid email';
END IF
Adam
  • 171
  • 2
  • 10
  • 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) – Madhur Bhaiya Sep 07 '18 at 16:53
  • I really have no clue what that entire article says. Do I just need to replace every `?` with a `*`? – Adam Sep 07 '18 at 16:54
  • "POSIX regexes don't support using the question mark ? as a non-greedy (lazy) modifier to the star and plus quantifiers like PCRE (Perl Compatible Regular Expressions). This means you can't use +? and *?" – Madhur Bhaiya Sep 07 '18 at 16:56
  • @Adam No. You're going to have to study it until you understand the topic. Some work required. – Lightness Races in Orbit Sep 07 '18 at 16:56
  • Would replacing the `+?` and `*?` with simple `*` work? – Adam Sep 07 '18 at 17:22
  • MySQL (before 8.0) has a rather limited REGEXP. Many constructs in what you have will fail. MariaDB 10 does much better. – Rick James Oct 01 '18 at 03:16

0 Answers0