Argument | Category | Description |
---|---|---|
Mode | Required | Specifies the pattern-matching mode:
|
Pattern | Required | Specifies the pattern for which the function searches. You compose pattern with the symbols that you define in the Symbols argument, operators, and parentheses. The following table describes the simplest patterns, which you can combine to form more complex patterns. When patterns have multiple operators, the function applies them in order of precedence, and applies operators of equal precedence from left to right. The following table also shows operator precedence. To force the function to evaluate a subpattern first, enclose it in parentheses. To specify that a subpattern must appear a specific number of times, use the Range-Matching Feature. For pattern matching details, refer to Patterns. |
Symbols | Required | Defines the symbols that appear in the values of the Pattern and Result arguments. The col_expr is an expression whose value is a column name, symbol is any valid identifier, and symbol_predicate is a SQL predicate (often a column name). For example, the Symbols argument for analyzing website visits might look like this: Symbols ( pagetype = 'homepage' AS H, pagetype <> 'homepage' AND pagetype <> 'checkout' AS PP, pagetype = 'checkout' AS CO ) The symbol is case-insensitive; however, a symbol of one or two uppercase letters is easy to identify in patterns. If col_expr represents a column that appears in multiple input tables, then you must qualify the ambiguous column name with its table name. For example: Symbols ( weblog.pagetype = 'homepage' AS H, weblog.pagetype = 'thankyou' AS T, ads.adname = 'xmaspromo' AS X, ads.adname = 'realtorpromo' AS R ) For more information about symbols that appear in the Pattern argument value, refer to Symbols. For more information about symbols that appear in the Result argument value, refer to Results. |
Filter | Optional | Specifies filters to impose on the matched rows. The function combines the filter expressions using the AND operator. The filter_expression syntax is: symbol_expression comparison_operator symbol_expression
The two symbol expressions must be type-compatible. The symbol_expression syntax is: { FIRST | LAST }(column_with_expression OF [ANY](symbol[,...])) The column_with_expression cannot contain the operator AND or OR, and all its columns must come from the same input. If the function has multiple inputs, then column_with_expression and symbol must come from the same input. The comparison_operator is either <, >, <=, >=, =, or !=. This argument can improve or degrade nPath performance, depending on several factors. For details, refer to Filters. |
Result | Required | Defines the output columns. The col_expr is an expression whose value is a column name; it specifies the values to retrieve from the matched rows. The function applies aggregate_function to these values. For details, see Results. The function evaluates this argument once for every matched pattern in the partition (that is, it outputs one row for each pattern match). |
In the following table, A and B are symbols defined in the Symbols argument.
pattern | Description | Operator Precedence |
---|---|---|
A | The function returns the rows that contain exactly one occurrence of A. | 1 (highest) |
A. | The function returns the rows that contain exactly one occurrence of A. | 1 |
A? | The function returns the rows that contain at most one occurrence of A. The ? operator is nongreedy. | 1 |
A* | The function returns the rows that contain zero or more occurrences of A. The * operator is nongreedy. | 1 |
A+ | The function returns the rows that contain at least one occurrence of A. The + operator is nongreedy. | 1 |
A.B | Cascade operator. The function returns the rows that contain A followed immediately by B. | 2 |
A|B | Alternative (or) operator. The function returns the rows that contain either A or B. | 3 |
^A | Startanchor operator. The function returns the rows that start with A. | |
A$ | Endanchor operator. The function returns the rows that end with A. |