Generic character/String JDBC Escape Syntax

Lance Andersen - Oracle Lance.Andersen at
Wed Oct 30 10:44:24 PDT 2013

Oracle 12 is introducing  MATCH_RECOGNIZE:

An example would be



           A.c1 as firstW,
           last(Z) as lastZ
       PATTERN(A? W+ X+ Y+ Z+)
           W as W.c2 < prev(W.c2),
           X as X.c2 > prev(X.c2),
           Y as Y.c2 < prev(Y.c2),
           Z as Z.c2 > prev(Z.c2)
   ) as T

The PATTERN clause in MATCH_RECOGNIZE has a PATTERN_QUANTIFIER that can be any of '*', '+', '?', '*?', '+?', or '??'. These are tokens, not literals or comments.

The Oracle JDBC driver team has requested that we provide a standardized way to escape a character or character string allowing for example to escape a '?' for PreparedStatements in order to be able to support MATCH_RECOGNIZE.  After some discussion, it is felt that a generic way to escape would be best vs. something specific for MATCH_RECOGNIZE

Here was their proposal proposal:


expands to the characters between '{\' and '\}'. Doubled backslashes '\' expand to a single backslash.

 {\?\}  expands to ?
 {\{fn sysdate()}\} expands to {fn sysdate()} and is not further expanded
 {\}\} expands to }
 {\\\}\} expands to \}


Today,  we have {Escape} for the Like clause but this would be more general.

Currently Oracle is the only database that supports this, but I would expect others to follow suit if this becomes popular.  It would be good to have a vendor neutral way to specify the escape vs an Oracle JDBC driver specific way.

Input is welcome.


Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
Oracle Java Engineering 
1 Network Drive 
Burlington, MA 01803
Lance.Andersen at

More information about the jdbc-spec-discuss mailing list