Applies to VAST Cluster 5.5.
Prefix, suffix and substring tests, pattern matching with LIKE, ILIKE, GLOB and SIMILAR TO, and splitting strings.
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.
Search and split
| Function |
What it does |
| split_part |
Split a VARCHAR on a separator and return one field by its position, counting from the start or the end. |
Searching
| Function |
What it does |
| contains |
Test whether a VARCHAR contains a given substring anywhere; the same name also tests lists and maps. |
ends_with (also suffix) |
Test whether a VARCHAR ends with a given suffix; suffix is the same function. |
starts_with (also prefix, ^@) |
Test whether a VARCHAR begins with a given prefix; prefix and the ^@ operator are the same test. |
strpos (also instr, position(substring IN s)) |
Find the position of the first occurrence of a substring in a VARCHAR; instr and position(substring IN s) do the same. |
Pattern matching
| Function |
What it does |
GLOB (also ~~~) |
Match a VARCHAR against a shell-style pattern with *, ? and [...]; ~~~ is the operator form. |
ILIKE and NOT ILIKE (also ~~*, ILIKE, !~~*, NOT ILIKE) |
Match a VARCHAR against a LIKE pattern without regard to case, with an optional ESCAPE character; ~~* and !~~* are the operator forms. |
| like_escape |
Match a VARCHAR against a LIKE pattern with an escape character, as a function; not_like_escape returns the opposite. |
LIKE and NOT LIKE (also ~~, LIKE, !~~, NOT LIKE) |
Match a VARCHAR against a pattern with % and _ wildcards, with an optional ESCAPE character; ~~ and !~~ are the operator forms. |
| ilike_escape |
Match a VARCHAR against a case-insensitive LIKE pattern with an escape character, as a function; not_ilike_escape returns the opposite. |
Splitting
| Function |
What it does |
string_split (also str_split, split, string_to_array) |
Split a VARCHAR on a literal separator into a list of parts; str_split, split and string_to_array are the same function. |
See also