SQL Row Pattern Matching: Implementing "Row-Level Regex" with MATCH_RECOGNIZE

When Rows Need Their Own "Regex"

Imagine you're on a security team with a table of login attempt records: each row is a login event containing a user ID, timestamp, and result (success or failure). A classic requirement emerges — find sequences where "multiple consecutive failures are immediately followed by a success," a telltale signal that a brute-force attack has succeeded.

Handling this kind of problem with traditional SQL is painful. You need window functions, self-joins, and layers of subqueries just to describe the cross-row sequential relationship of "several failure events followed by a success event." The code grows longer, the logic becomes harder to read, and maintenance turns into a nightmare.

The root issue is this: SQL naturally excels at set operations, but struggles to express "ordered patterns across rows." That's precisely where regular expressions shine — except regex matches character sequences, and what we really want is a tool that can match row sequences.

MATCH_RECOGNIZE: Bringing Regex Thinking to Row Sequences

MATCH_RECOGNIZE was purpose-built to address this pain point. It's a row pattern recognition clause defined in the SQL standard, and its core idea can be summarized in one sentence: match consecutive rows in a table the same way you'd write a regular expression.

Its key components consist of several essential clauses:

PARTITION BY and ORDER BY

PARTITION BY determines the grouping dimension — for example, partitioning by user ID so that each user's login event sequence is analyzed independently without interference from other users. ORDER BY defines the row ordering, which is critical for pattern matching; typically you order by timestamp to ensure events are examined row by row in the order they occurred.

DEFINE and PATTERN

DEFINE is used to name and define the conditions for "row states." For example, you can define FAIL AS result = 'failure' and SUCCESS AS result = 'success', classifying each row into a pattern variable.

PATTERN is the soul of the entire feature — it uses regex-like syntax to describe the order in which these state variables should appear. To express "one or more failures followed by a success," you simply write PATTERN (FAIL+ SUCCESS). The + here carries exactly the same meaning as in regex: "one or more times."

MEASURES and Output Modes

The MEASURES clause extracts the information you care about from the matched row sequence — such as the failure count, the time of the first failure, or the time of the final success. ONE ROW PER MATCH and ALL ROWS PER MATCH control output granularity: the former returns one summary row per match, while the latter returns every individual row within the matched range.

Within MEASURES, you can use the CLASSIFIER() function to retrieve the pattern variable name each row was assigned to, and MATCH_NUMBER() to number multiple matches within the same partition — very useful for analyzing whether a user has triggered the same behavioral pattern multiple times. FIRST() and LAST() are common helper functions in MEASURES that retrieve field values from the first and last matched rows of a given pattern variable, respectively. PATTERN also supports quantifiers that closely mirror regex: * (zero or more), + (one or more), ? (zero or one), and precise count controls like {n} and {n,m}, making it equally concise to describe patterns like "exactly 5 failures" or "between 2 and 10 failures."

A Leap in Readability

Putting all of this together, a query to detect "a successful brute-force attack" looks roughly like this:

SELECT *
FROM login_attempts
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    FIRST(FAIL.event_time) AS first_fail,
    SUCCESS.event_time AS breach_time,
    COUNT(FAIL.*) AS fail_count
  ONE ROW PER MATCH
  PATTERN (FAIL+ SUCCESS)
  DEFINE
    FAIL AS result = 'failure',
    SUCCESS AS result = 'success'
) AS mr;

Compared to a traditional approach filled with self-joins and window functions, this query is nearly readable as plain English: partition by user, order by time, find the pattern of "multiple failures followed by a success," and report the time of the first failure, the breach time, and the failure count. The intent is immediately clear — which is exactly what declarative SQL should look like.

Where It Fits

The value of row pattern recognition extends far beyond security auditing. Any analysis involving "event ordering" can benefit:

• Financial risk control: Detecting anomalous transaction sequences on an account, such as multiple small probe transactions in a short window followed by a large transfer.
• User behavior analysis: Tracking complete conversion funnel paths like "browse → add to cart → purchase," or identifying behavioral patterns that precede churn.
• IoT and monitoring: Detecting abnormal waveforms in sensor time-series data, such as readings that rise steadily and then drop suddenly.
• Stock price analysis: Identifying classic technical patterns such as V-shaped reversals or consecutive up-days in price movements.

What these scenarios share is this: the focus isn't on individual rows, but on patterns that unfold across multiple rows over time. This is precisely the blind spot that ordinary aggregation and window functions struggle to express elegantly.

Real-World Support

It's worth noting that while MATCH_RECOGNIZE is part of the SQL standard, support varies considerably across databases and data processing engines. Oracle, Snowflake, Trino/Presto, and Apache Flink have all implemented it, while popular open-source databases like PostgreSQL and MySQL do not yet natively support this syntax. Before diving in, always verify that your engine is compatible.

For data engineers and analysts, mastering MATCH_RECOGNIZE means gaining a powerful tool for tackling "sequential pattern" problems. It compresses logic that would otherwise require extensive procedural code into a concise, declarative description — much like handing off tedious string traversal to a regular expression, making complex row sequence matching intuitive and maintainable.

MATCH_RECOGNIZE was first incorporated into the SQL:2016 standard (ISO/IEC 9075-2:2016), making it one of the most significant additions in that version. Oracle Database 12c Release 2 was the first mainstream database to ship an implementation; Snowflake followed with support around 2021; and the stream processing engine Apache Flink added support relatively early given its natural focus on event stream scenarios. It's worth noting that Flink's implementation differs from batch-processing databases in some ways — for example, in how it handles unbounded streams and how timeout semantics are defined — so cross-engine query migration requires careful review of the documentation. For PostgreSQL users where this syntax isn't yet supported, the community has developed workarounds using recursive CTEs or window functions, though these come with significantly higher code complexity and inconsistent performance.