MATCH_RECOGNIZE

MATCH_RECOGNIZE is a SQL statement in the Oracle Specific category. Oracle 12c+: pattern matching in SQL — finds patterns across rows (like regex but for tabular data). The syntax is SELECT * FROM table MATCH_RECOGNIZE (PARTITION BY col ORDER BY col MEASURES ... PATTERN ...);. It returns pattern-matched rows. A typical example: SELECT * FROM stock_prices MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY trade_date MEASURES FIRST(price) AS start_price, LAST(price) AS end_price PATTERN (up up+) DEFINE up AS price > PREV(price) ); -- Finds consecutive increasing price runs A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c). A close relative is SYSDATE / CURRENT_DATE, which oracle: returns the current date and time of the database server.