title: Using java based regular expression to query Oracle
body: <p>I have to write a query where a regular expression which is java based (i believe POSIX standard) is to send to a stored procedure. Which will use this regex string to query database to fetch only those records which statisfy the regex. </p>
  
  <p>I was reading about <a href="http://www.regular-expressions.info/oracle.html" rel="nofollow">Regular Expressions</a> and i saw that oracle regular expression's aren't same as Java i.e. </p>
  
  <blockquote>
    <p>With version 10g, Oracle Database offers 4 regexp functions that you
    can use in SQL and PL/SQL statements. These functions implement the
    POSIX Extended Regular Expressions (ERE) standard. Oracle fully
    supports collating sequences and equivalence classes in bracket
    expressions. The NLS_SORT setting determines the POSIX locale used,
    which determines the available collating sequences and equivalence
    classes.</p>
    
    <p>Oracle does not implement the POSIX ERE standard exactly, however. It
    deviates in three areas. First, Oracle supports the backreferences \1
    through \9 in the regular expression. The POSIX ERE standard does not
    support these, even though POSIX BRE does. In a fully compliant
    engine, \1 through \9 would be illegal. The POSIX standard states it
    is illegal to escape a character that is not a metacharacter with a
    backslash. Oracle allows this, and simply ignores the backslash. E.g.
    \z is identical to z in Oracle. The result is that all POSIX ERE
    regular expressions can be used with Oracle, but some regular
    expressions that work in Oracle may cause an error in a fully
    POSIX-compliant engine. Obviously, if you only work with Oracle, these
    differences are irrelevant.</p>
    
    <p>The third difference is more subtle. It won't cause any errors, but
    may result in different matches. As I explained in the topic about the
    POSIX standard, it requires the regex engine to return the longest
    match in case of alternation. Oracle's engine does not do this. It is
    a traditional NFA engine, like all non-POSIX regex flavors discussed
    on this website.</p>
    
    <p>If you've worked with regular expressions in other programming
    languages, be aware that POSIX does not support non-printable
    character escapes like \t for a tab or \n for a newline. You can use
    these with a POSIX engine in a programming language like C++, because
    the C++ compiler will interpret the \t and \n in string constants. In
    SQL statements, you'll need to type an actual tab or line break in the
    string with your regular expression to make it match a tab or line
    break. Oracle's regex engine will interpret the string '\t' as the
    regex t when passed as the regexp parameter.</p>
  </blockquote>
  
  <p>Just wondering if there is some sort of help that i can use to tackle this issue? Or do i have to transfer Java regex into PLSQL regex?</p>
  
  <p>The regex which we are going to use is going to be used widely within Java. There are few usecases where the same regex would be applied to oracle Stored Procedures. One of the sample <strong>regex</strong> is <code>.*__.*__^ABC</code></p>
  
  <p>I believe, it would be way more helpful if i can know what are incompatibilities in these 2 versions :)</p>