55811

Converting PCRE to POSIX regular expression

Question:

<strong>I am working on a MySQL database and noticed that it doesn't natively support PCRE (requires a plugin).</strong>

I wish to use these three for some data validation (these are actually the values given to the pattern attribute):

<ol><li>^[A-z\. ]{3,36}</li> <li>^[a-z\d\.]{3,24}$</li> <li>^(?=^.{4,}$)(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?!.*\s).*$</li> </ol>

<strong>How do I do this?</strong><br /> 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.

Answer1:

The <a href="http://dev.mysql.com/doc/refman/5.7/en/regexp.html" rel="nofollow">MySQL docs</a> state that:

<blockquote>

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

</blockquote>

Ok, so we're talking about POSIX ERE.

<a href="http://www.regular-expressions.info/reference.html" rel="nofollow">This page</a> lists the details between various regex flavors, so I'll use it as a cheatsheet.

<hr /><ol><li>

^[A-z\. ]{3,36}

You're using:

<ul><li>Anchors: ^</li> <li>Character classes: [...]</li> <li>The range quantifier: {n,m}</li> </ul>

All of these are supported out of the box in POSIX ERE, so you can use this expression as-is. <strong>But</strong> 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} </li> <li>

^[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}$ </li> <li>

^(?=^.{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]' </li> </ol>

Recommend

  • JSON Multi dimensional Array to Java Multi-Dimensional Array
  • jqGrid How to Delete Multiple Selected rows after submitting to a Database?
  • How to write a AJAX GET request with two parameters taken from html file?
  • Combining multiple ggplot2 polygons / paths
  • Matlab imwrite() quality
  • is there anyway to pass back additional data in jqgrid treeGrid url request?
  • How to make Java compiler generate line numbers in compiled code
  • npm thinks node is out of date, but it isn't
  • Deploy same Javascript webapp build to different environments
  • Performance difference between accessing local and class member variables
  • javascript add operation returns bad result
  • Why is it ambiguous to call overloaded ambig(long) and ambig(unsigned long) with an integer literal?
  • WebApp in AppServices vs CloudService
  • Swipe Gesture Recognizer not working for me
  • Android application not restoring state when installed from .apk, works fine from eclipse
  • Xaml, wpf image position and crop issue
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • Python cosine function precision [duplicate]
  • HttpClient: disabling chunked encoding
  • How Get arguments value using inline assembly in C without Glibc?
  • How to make R's read_csv2() recognise the text characters properly
  • Tell Git to stop prompting me for conflicts when none really exist?
  • Trying to get the char code of ENTER key
  • Implementation of State Monad
  • Access Android Market through SSH tunnel
  • Tamper-proof configuration files in .NET?
  • Zurb Foundation _global.scss meta styles for js?
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • ImageMagick, replace semi-transparent white with opaque white
  • NetLogo BehaviorSpace - Measure runs using reporters
  • swift auto completion not working in Xcode6-Beta
  • Excel - Autoshape get it's name from cell (value)
  • WinForms: two way TextBox problem
  • ActionScript 2 vs ActionScript 3 performance
  • QuartzCore.framework for Mono Develop
  • RestKit - RKRequestDelegate does not exist
  • how does django model after text[] in postgresql [duplicate]
  • java string with new operator and a literal