I am working on a MySQL database and noticed that it doesn't natively support PCRE (requires a plugin).
I wish to use these three for some data validation (these are actually the values given to the pattern attribute):
^[A-z\. ]{3,36}^[a-z\d\.]{3,24}$^(?=^.{4,}$)(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?!.*\s).*$
How do I do this?
I looked on the web but couldn't find any concrete examples or answers. Also there seem to exist no utilities that could do this automatically.
I am aware that some times, such conversions are not exact and can produce differences but I am willing to try.
Best How To :
The MySQL docs state that:
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.
Ok, so we're talking about POSIX ERE.
This page lists the details between various regex flavors, so I'll use it as a cheatsheet.
^[A-z\. ]{3,36}
You're using:
- Anchors:
^
- Character classes:
[...]
- The range quantifier:
{n,m}
All of these are supported out of the box in POSIX ERE, so you can use this expression as-is. But escaping the . in the character class is redundant, and A-z is most probably wrong in a character class (it includes [\]^_\`), so just write:
^[A-Za-z. ]{3,36}
^[a-z\d\.]{3,24}$
This one uses \d as well, which is unsupported in POSIX ERE. So you have to write:
^[a-z0-9.]{3,24}$
^(?=^.{4,}$)(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?!.*\s).*$
Meh. You're using lookaheads. These are totally out of the scope for POSIX ERE, but you can work around this limitation by combining several SQL clauses for an equivalent logic:
WHERE LENGTH(foo) >= 4
AND foo REGEXP '[0-9]'
AND foo REGEXP '[a-z]'
AND foo REGEXP '[A-Z]'
AND NOT foo REGEXP '[ \t\r\n]'
