17.05 - nPath Patterns - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Analytic Functions

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1206-170K

The value of the Pattern syntax element specifies the sequence of rows for which the function searches. You compose the pattern definition, pattern, with symbols (which you define in the Symbols syntax element), operators, and parentheses. In the pattern definition, symbols represent rows. You can combine symbols with pattern operators to define simple or complex patterns of rows for which to search.

The following table lists and describes the basic pattern operators, in decreasing order of precedence. In the table, A and B are symbols that have been defined in the Symbols syntax element.

Basic Pattern Operators
Operator Description Precedence
A
Matches one row that meets the definition of A. 1 (highest)
A.
Matches one row that meets the definition of A. 1
A?
Matches 0 or 1 rows that satisfy the definition of A. 1
A*
Matches 0 or more rows that satisfy the definition of A (greedy operator). 1
A+
Matches 1 of more rows that satisfy the definition of A (greedy operator). 1
A.B
Matches two rows, where the first row meets the definition of A and the second row meets the definition of B. 2
A|B
Matches one row that meets the definition of either A or B. 3

The nPath function uses greedy pattern matching. That is, it finds the longest available match when matching patterns specified by nongreedy operators. For more information, see nPath Greedy Pattern Matching.

These examples show the pattern operator precedence rules:
  • A.B+ is the same as A.(B+)
  • A|B* is the same as A|(B*)
  • A.B|C is the same as (A.B)|C

Example:

A.(B|C)+.D?.X*.A

The preceding pattern definition matches any set of rows whose first row meets the definition of symbol A, followed by a nonempty sequence of rows, each of which meets the definition of either symbol B or C, optionally followed by one row that meets the definition of symbol D, followed by any number of rows that meet the definition of symbol X, and ending with a row that meets the definition of symbol A.

You can use parentheses to define precedence rules. Parentheses are recommended for clarity, even where not strictly required.

To indicate that a sequence of rows must start or end with a row that matches a certain symbol, use the start anchor (^) or end anchor ($) operator.

Start Anchor and End Anchor Pattern Operators
Operator Description
^A
Appears only at the beginning of a pattern. Indicates that a set of rows must start with a row that meets the definition of A.
A$
Appears only at the end of a pattern. Indicates that a set of rows must end with a row that meets the definition of A.

Subpattern operators let you specify how often a subpattern must appear in a match. You can specify a minimum number, exact number, or range. In the following table, X represents any pattern definition composed of symbols and any of the previously described pattern operators.

Subpattern Operators
Operator Description
(X){a}
Matches exactly a occurrences of the pattern X.
(X){a,}
Matches at least a occurrences of the pattern X.
(X){a,b}
Matches at least a and no more than b occurrences of the pattern X.