Applies to VAST Cluster 5.5.
Matching, extracting, replacing and splitting with regular expressions, and the pattern syntax.
Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.
Splitting
| Function | What it does |
|---|---|
regexp_split_to_array (also string_split_regex, str_split_regex) |
Split a VARCHAR at every match of a regular expression into a list of parts; string_split_regex and str_split_regex are the same function. |
| regexp_split_to_table | Split a VARCHAR at every match of a regular expression and return one row for each part, in the SELECT list. |
Pattern matching
| Function | What it does |
|---|---|
| SIMILAR TO | Test whether a whole VARCHAR matches a regular expression with SIMILAR TO; NOT SIMILAR TO returns the opposite. |
Regular expressions
| Function | What it does |
|---|---|
| regexp_extract_all | Return every match of a regular expression in a VARCHAR, or one group of every match, as a list. |
| regexp_extract | Return the first match of a regular expression in a VARCHAR, one of its groups, or a STRUCT of named groups. |
regexp_full_match (also ~, regexp_full_match(s, pattern), !~, NOT regexp_full_match(s, pattern)) |
Test whether a regular expression matches a whole VARCHAR; the ~ operator is the same test and !~ its negation. |
| regexp_matches | Test whether a regular expression matches anywhere in a VARCHAR, with options such as case-insensitive matching. |
| regexp_replace | Replace the first match, or with option g every match, of a regular expression in a VARCHAR, with group references in the replacement. |
| Regular-expression syntax and options | The pattern syntax, the options string and the unsupported pattern features of the regular-expression functions. |
| regexp_escape | Escape the characters that have a meaning in a regular expression, so that a string from a column or a parameter is matched literally by the VAST Query Engine. |