Text basics

Applies to VAST Cluster 5.5.

Length, case, concatenation, parts of a string, replacing, code points, formatting, Unicode and URLs, string similarity and file paths.

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.

Characters and code points

Function	What it does
ascii	Return the Unicode code point of the first character of a VARCHAR, including code points above 127.
chr	Return the character with a given Unicode code point as a VARCHAR, and the errors for numbers that aren't valid code points.
ord (also `unicode`)	Return the Unicode code point of the first character of a VARCHAR, or -1 for an empty string; unicode is the same function.

Length

Function	What it does
bit_length	Count the bits in the UTF-8 encoding of a VARCHAR, or the bits of a BIT value.
length_grapheme	Count the grapheme clusters in a VARCHAR, what a reader sees as characters.
length (also `len`)	Count the characters in a VARCHAR, the bits in a BIT value or the elements of a list; len is the same function.
strlen	Count the bytes in the UTF-8 encoding of a VARCHAR.

Case

Function	What it does
COLLATE nocase and noaccent	Compare strings without regard to case, accents or both with COLLATE nocase, noaccent and nocase.noaccent.
lower (also `lcase`)	Convert a VARCHAR to lower case, including letters outside ASCII; lcase is the same function.
upper (also `ucase`)	Convert a VARCHAR to upper case, including letters outside ASCII; ucase is the same function.

Concatenation

Function	What it does
\|\| (concatenation)	Join two strings, two lists or two BLOB values with the \|\| operator; a NULL on either side gives NULL.
concat_ws	Join values into one VARCHAR with a separator between them, skipping NULL values.
concat	Join values into one string, skipping NULLs, or join lists.

Similarity

Function	What it does
damerau_levenshtein	Return the edit distance between two strings, counting a swap of two neighbouring characters as one edit.
hamming (also `mismatches`)	Count the positions at which two strings of the same length differ; mismatches is the same function.
jaccard	Return the Jaccard similarity of the sets of characters in two strings, from 0 to 1.
jaro_similarity	Score how alike two strings are, from 0.0 to 1.0, with the Jaro similarity, optionally with a cutoff below which the score is 0.0.
jaro_winkler_similarity	Score how alike two strings are with the Jaro-Winkler similarity: the Jaro score, raised for strings that start the same way, with an optional cutoff.
levenshtein (also `editdist3`)	Return the edit distance between two strings: the fewest insertions, deletions and substitutions that turn one into the other; editdist3 is the same function.

Formatting

Function	What it does
format	Build a VARCHAR from a template with {} placeholders, positions and format specifications for width, alignment, decimals and number bases.
printf	Build a VARCHAR from a C-style template with %s, %d, %f, %x and other conversions, widths and precisions.

Parts of a string

Function	What it does
left_grapheme	Return the first n grapheme clusters of a VARCHAR, so accented letters and flag emoji are never cut in half.
left	Return the first n characters of a VARCHAR, or all but the last n with a negative count.
right_grapheme	Return the last n grapheme clusters of a VARCHAR, so accented letters and flag emoji are never cut in half.
right	Return the last n characters of a VARCHAR, or all but the first n with a negative count.
substring_grapheme	Return part of a VARCHAR by start position and length, counted in grapheme clusters, so accented letters and flag emoji are never cut in half.
substring (also `substr`, `SUBSTRING(s FROM start FOR length)`, `substring(s, start, length)`)	Return part of a VARCHAR by start position and length; substr, SUBSTRING(s FROM a FOR b) and the s[start:end] and s[i] slices do the same.

Unicode and URLs

Function	What it does
nfc_normalize	Convert a string to Unicode normalization form C, so a letter and a combining accent become one character and equal-looking text compares equal.
strip_accents	Remove accents from the letters of a string, for accent-insensitive search and matching.
url_decode	Decode a percent-encoded URL string: each % and two hexadecimal digits becomes the byte it stands for.
url_encode	Percent-encode a string for use in a URL: every byte other than letters, digits and - . _ ~ becomes % and two hexadecimal digits.

Paths

Function	What it does
parse_dirname	Get the top-level directory of a file path: the first component, or / for an absolute path.
parse_dirpath	Get the directory part of a file path: everything before the last separator.
parse_filename	Get the file name at the end of a path, optionally without its last extension and with a chosen separator.
parse_path	Split a file path into a list of its components, splitting on / and \ by default or on one separator you choose.

Changing text

Function	What it does
repeat	Repeat a VARCHAR, a BLOB or a list a number of times.
replace	Replace every occurrence of a substring in a VARCHAR with another string, without patterns.
reverse	Reverse the characters of a VARCHAR, keeping accented letters and flag emoji whole.
translate	Replace or remove single characters of a VARCHAR, each by the character at the same position in a second string.