Generic character/String JDBC Escape Syntax

[Lance Andersen - Oracle]

Oracle 12 is introducing  MATCH_RECOGNIZE:  http://docs.oracle.com/cd/E12839_01/doc.1111/e12048/pattern_recog.htm#autoId6


An example would be

---------


Example:

   SELECT
       T.firstW,
       T.lastZ
   FROM
       S2
   MATCH_RECOGNIZE (
       MEASURES
           A.c1 as firstW,
           last(Z) as lastZ
       PATTERN(A? W+ X+ Y+ Z+)
       DEFINE
           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.

Best
Lance



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