PATTERN clause for MATCH_RECOGNIZE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19.0 and above

Important

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Azure Databricks previews.

Specifies the pattern to match in a MATCH_RECOGNIZE operation.

Syntax

PATTERN ( row_pattern )

row_pattern
  { { pattern_primary | pattern_operator } [ pattern_quantifier ] }

pattern_primary
  { variable
  | $
  | ^ }

pattern_operator
  { ( [ row_pattern ] )
  | PERMUTE ( row_pattern [, ...] )
  | row_pattern | row_pattern
  | row_pattern row_pattern }

pattern_quantifier
  { *
  | +
  | ?
  | { n }
  | { min , [ max ] }
  | { , max } }

Parameters

Pattern primary

  • variable

    A variable defined in the DEFINE clause. An undefined variable matches all rows.

  • $

    The end of the partition.

  • ^

    The beginning of the partition.

    In this release, ^ and $ are supported only at the leading or trailing position of the top-level pattern. For example, ^ A B $ is supported, but mid-sequence anchors such as A ^ B and per-branch anchors such as (^ A) | (B $) are not supported.

Pattern operator

  • ( [ row_pattern ] )

    A grouping that overrides precedence.

  • PERMUTE ( row_pattern [, ...] )

    Matches row patterns in any permutation of order. PERMUTE requires at least two patterns. If you specify fewer than two, Azure Databricks raises MATCH_RECOGNIZE_PERMUTE_TOO_FEW_PATTERNS. If you specify more than the supported maximum, Azure Databricks raises MATCH_RECOGNIZE_PERMUTE_TOO_MANY_PATTERNS.

  • row_pattern | row_pattern

    Matches either of the row patterns.

  • row_pattern row_pattern

    Matches a sequence of row patterns.

Pattern quantifier

Specifies how often the pattern_primary must be matched:

  • *

    Zero or more times.

  • +

    One or more times.

  • ?

    At most once.

  • { n }

    Exactly n times. n must be an INTEGER literal greater than 0.

  • { min , [ max ] }

    At least min times, and if specified up to max times.

  • { , max }

    No more than max times.

If you omit a quantifier, the default is { 1 }: exactly one match.

Common error conditions

  • Last updated on