shingala sohil shingala sohil - 2 months ago 24
PHP Question

convert PCRE regular expression to mysql query regular expression

I have below regular expression

^(?!\d+$)(?!(?:[^$&%@]*[$&%@]){4})(?!.*\b\w{11}).{3,}$


it is working fine in PHP

I want that converted to MySQL supported query. when I run above regular expression. I got error 'repetition-operator operand invalid' from reg-exp

please help to solve this

Answer

MySQL does not support lookarounds, nor does it support \d or \w classes. You may split the regex like that:

SELECT * FROM table WHERE col REGEXP '^.{3,}$' AND col NOT REGEXP '^[0-9]+$' AND col NOT REGEXP '([^$&%@]*[$&%@]){4}' AND col NOT REGEXP '[[:<:]][[:alnum:]_]{11}'

Where:

  • REGEXP '^.{3,}$' - the total length should be 3 or more characters
  • AND col NOT REGEXP '^[0-9]+$' - string cannot consist of digits only
  • AND col NOT REGEXP '([^$&%@]*[$&%@]){4}' - string cannot have 4 special chars
  • AND col NOT REGEXP '[[:alnum:]_]{11}' - string cannot have a word with 11 chars
Comments