Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

String

Prev Next

Text functions on VARCHAR values, in four pages. Start with Text basics for length, case, substrings and replacing; Search and split covers finding text and splitting it, Trimming and padding the fixed-width forms, and Regular expressions the regexp functions.

  • Text basics: Length, case, concatenation, substrings, replacing, formatting, similarity and path functions on VARCHAR values in the VAST Query Engine.
  • Search and split: Find text inside VARCHAR values with prefix, suffix, substring and LIKE tests, and split strings into parts or lists.
  • Trimming and padding: Remove leading and trailing characters from VARCHAR values, or pad them to a fixed width, in the VAST Query Engine.
  • Regular expressions: Match, extract, replace and split VARCHAR values with regular expressions in the VAST Query Engine, with the pattern syntax and options.
  • ascii: Return the Unicode code point of the first character of a VARCHAR in the VAST Query Engine, including code points above 127.
  • chr: Return the character with a given Unicode code point as a VARCHAR in the VAST Query Engine, and the errors for numbers that aren't valid code points.
  • ord: Return the Unicode code point of the first character of a VARCHAR, or -1 for an empty string, in the VAST Query Engine; unicode is the same function.
  • bit_length: Count the bits in the UTF-8 encoding of a VARCHAR, or the bits of a BIT value, in the VAST Query Engine.
  • length_grapheme: Count the grapheme clusters in a VARCHAR, what a reader sees as characters, in the VAST Query Engine.
  • length: Count the characters in a VARCHAR, the bits in a BIT value or the elements of a list in the VAST Query Engine; len is the same function.
  • strlen: Count the bytes in the UTF-8 encoding of a VARCHAR in the VAST Query Engine.
  • COLLATE nocase and noaccent: Compare strings without regard to case, accents or both with COLLATE nocase, noaccent and nocase.noaccent in the VAST Query Engine.
  • lower: Convert a VARCHAR to lower case, including letters outside ASCII, in the VAST Query Engine; lcase is the same function.
  • upper: Convert a VARCHAR to upper case, including letters outside ASCII, in the VAST Query Engine; ucase is the same function.
  • || (concatenation): Join two strings, two lists or two BLOB values with the || operator in the VAST Query Engine; a NULL on either side gives NULL.
  • concat_ws: Join values into one VARCHAR with a separator between them in the VAST Query Engine, skipping NULL values.
  • concat: Join values into one string, skipping NULLs, or join lists, in the VAST Query Engine.
  • damerau_levenshtein: Return the edit distance between two strings in the VAST Query Engine, counting a swap of two neighbouring characters as one edit.
  • hamming: Count the positions at which two strings of the same length differ in the VAST Query Engine; mismatches is the same function.
  • jaccard: Return the Jaccard similarity of the sets of characters in two strings in the VAST Query Engine, from 0 to 1.
  • jaro_similarity: Score how alike two strings are, from 0.0 to 1.0, with the Jaro similarity in the VAST Query Engine, 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 in the VAST Query Engine: the Jaro score, raised for strings that start the same way, with an optional cutoff.
  • levenshtein: Return the edit distance between two strings in the VAST Query Engine: the fewest insertions, deletions and substitutions that turn one into the other; editdist3 is the same function.
  • format: Build a VARCHAR from a template with {} placeholders, positions and format specifications for width, alignment, decimals and number bases in the VAST Query Engine.
  • printf: Build a VARCHAR from a C-style template with %s, %d, %f, %x and other conversions, widths and precisions in the VAST Query Engine.
  • left_grapheme: Return the first n grapheme clusters of a VARCHAR in the VAST Query Engine, so accented letters and flag emoji are never cut in half.
  • left: Return the first n characters of a VARCHAR in the VAST Query Engine, or all but the last n with a negative count.
  • right_grapheme: Return the last n grapheme clusters of a VARCHAR in the VAST Query Engine, so accented letters and flag emoji are never cut in half.
  • right: Return the last n characters of a VARCHAR in the VAST Query Engine, 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, in the VAST Query Engine, so accented letters and flag emoji are never cut in half.
  • substring: Return part of a VARCHAR by start position and length in the VAST Query Engine; substr, SUBSTRING(s FROM a FOR b) and the s[start:end] and s[i] slices do the same.
  • nfc_normalize: Convert a string to Unicode normalization form C in the VAST Query Engine, 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 in the VAST Query Engine, for accent-insensitive search and matching.
  • url_decode: Decode a percent-encoded URL string in the VAST Query Engine: each % and two hexadecimal digits becomes the byte it stands for.
  • url_encode: Percent-encode a string for use in a URL in the VAST Query Engine: every byte other than letters, digits and - . _ ~ becomes % and two hexadecimal digits.
  • parse_dirname: Get the top-level directory of a file path in the VAST Query Engine: the first component, or / for an absolute path.
  • parse_dirpath: Get the directory part of a file path in the VAST Query Engine: everything before the last separator.
  • parse_filename: Get the file name at the end of a path in the VAST Query Engine, optionally without its last extension and with a chosen separator.
  • parse_path: Split a file path into a list of its components in the VAST Query Engine, splitting on / and \ by default or on one separator you choose.
  • repeat: Repeat a VARCHAR, a BLOB or a list a number of times in the VAST Query Engine.
  • replace: Replace every occurrence of a substring in a VARCHAR with another string in the VAST Query Engine, without patterns.
  • reverse: Reverse the characters of a VARCHAR in the VAST Query Engine, 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, in the VAST Query Engine.
  • split_part: Split a VARCHAR on a separator and return one field by its position, counting from the start or the end, in the VAST Query Engine.
  • contains: Test whether a VARCHAR contains a given substring anywhere in the VAST Query Engine; the same name also tests lists and maps.
  • ends_with: Test whether a VARCHAR ends with a given suffix in the VAST Query Engine; suffix is the same function.
  • starts_with: Test whether a VARCHAR begins with a given prefix in the VAST Query Engine; prefix and the ^@ operator are the same test.
  • strpos: Find the position of the first occurrence of a substring in a VARCHAR in the VAST Query Engine; instr and position(substring IN s) do the same.
  • GLOB: Match a VARCHAR against a shell-style pattern with *, ? and [...] in the VAST Query Engine; ~~~ is the operator form.
  • ILIKE and NOT ILIKE: Match a VARCHAR against a LIKE pattern without regard to case in the VAST Query Engine, 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, in the VAST Query Engine; not_like_escape returns the opposite.
  • LIKE and NOT LIKE: Match a VARCHAR against a pattern with % and _ wildcards in the VAST Query Engine, 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, in the VAST Query Engine; not_ilike_escape returns the opposite.
  • string_split: Split a VARCHAR on a literal separator into a list of parts in the VAST Query Engine; str_split, split and string_to_array are the same function.
  • ltrim: Remove spaces, or any characters you list, from the start of a VARCHAR in the VAST Query Engine.
  • rtrim: Remove spaces, or any characters you list, from the end of a VARCHAR in the VAST Query Engine.
  • trim: Remove spaces, or any characters you list, from both ends of a VARCHAR in the VAST Query Engine; trim(BOTH | LEADING | TRAILING chars FROM s) is the standard SQL form.
  • lpad: Pad a VARCHAR on the left with a fill string to a given number of characters, or cut it to that length, in the VAST Query Engine.
  • rpad: Pad a VARCHAR on the right with a fill string to a given number of characters, or cut it to that length, in the VAST Query Engine.
  • regexp_split_to_array: Split a VARCHAR at every match of a regular expression into a list of parts in the VAST Query Engine; 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, in the VAST Query Engine.
  • SIMILAR TO: Test whether a whole VARCHAR matches a regular expression with SIMILAR TO in the VAST Query Engine; NOT SIMILAR TO returns the opposite.
  • regexp_extract_all: Return every match of a regular expression in a VARCHAR, or one group of every match, as a list in the VAST Query Engine.
  • regexp_extract: Return the first match of a regular expression in a VARCHAR, one of its groups, or a STRUCT of named groups, in the VAST Query Engine.
  • regexp_full_match: Test whether a regular expression matches a whole VARCHAR in the VAST Query Engine; the ~ operator is the same test and !~ its negation.
  • regexp_matches: Test whether a regular expression matches anywhere in a VARCHAR in the VAST Query Engine, 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 in the VAST Query Engine, 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 in the VAST Query Engine.
  • 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.
  • error: Stop a query with your own error message in the VAST Query Engine, for example inside a CASE to reject rows that break a rule.
  • bin: Write the bytes of a string, or an integer, as text of 0s and 1s in the VAST Query Engine; to_binary is the same function.
  • from_binary: Read a string of 0s and 1s into a BLOB in the VAST Query Engine, padded on the left to whole bytes; unbin is the same function.