match_recognize — Regular Expressions Over Rows

Use Cases

The match_recognize clause is useful to implement the following use cases:

  • Finding series of consecutive events (see slides)

  • Pattern matching: trend reversal, periodic events, …

  • Top-N per Group (see slides)


Row pattern matching was introduced by SQL:2016 with three optional features: R010 is the basis: this feature requires the match_recognize clause as explained above with the aggregate functions min, max, sum, count, avg. R020 allows using patterns in the over clause to define frames. R030 supports all aggregate functions for row patterns (e.g. stddev_pop, …).

BigQueryDb2 (LUW)MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLitefrom clausewindow clausefull aggregate support

JDBC and ORA-17041 Missing IN or OUT parameter

The character ? is used as placeholder in JDBC but is also a valid literal in the pattern clause (e.g., for non-greedy matching). Per default, the Oracle JDBC driver treats each ? as bind parameter. To use ? as literal in the pattern clause, the Oracle JDBC driver supports two methods:


This is a standard JDBC feature that disables any client side interpretation of the SQL string. It doesn’t work for PreparedStatements and thus disallows the use of bind variables.


The Oracle JDBC driver support a proprietary escape syntax for the question mark: {\?\}. This character sequence can be used in the pattern clause instead of a question mark. This method works for PreparedStatements.

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-⁠ on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at Previously he made, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license