SQL
Text Functions and Operators
This section describes functions and operators for examining and manipulating [`STRING` values](#docs:sql:data_types:text).  
<!-- markdownlint-disable MD056 -->  
| Name | Description |
|:--|:-------|
| [`string ^@ search_string`](#::string--search_string) | Return true if `string` begins with `search_string`. |
| [`string || string`](#::string--string) | Concatenate two strings. Any NULL input results in NULL. See also [`concat(string, ...)`](#::concatstring-). |
| [`string[index]`](#stringindex) | Extract a single character using a (1-based) index. |
| [`string[begin:end]`](#stringbeginend) | Extract a string using slice conventions, see [slicing](#docs:sql:functions:list::slicing). |
| [`string LIKE target`](#::string-like-target) | Returns true if the `string` matches the like specifier (see [Pattern Matching](#docs:sql:functions:pattern_matching)). |
| [`string SIMILAR TO regex`](#::string-similar-to-regex) | Returns `true` if the `string` matches the `regex`; identical to `regexp_full_match` (see [Pattern Matching](#docs:sql:functions:pattern_matching)). |
| [`array_extract(list, index)`](#::array_extractlist-index) | Extract a single character using a (1-based) index. |
| [`array_slice(list, begin, end)`](#::array_slicelist-begin-end) | Extract a string using slice conventions. Negative values are accepted. |
| [`ascii(string)`](#::asciistring) | Returns an integer that represents the Unicode code point of the first character of the `string`. |
| [`bar(x, min, max[, width])`](#barx-min-max-width) | Draw a band whose width is proportional to (` x - min`) and equal to `width` characters when `x` = `max`. `width` defaults to 80. |
| [`bit_length(string)`](#::bit_lengthstring) | Number of bits in a string. |
| [`chr(x)`](#::chrx) | Returns a character which is corresponding the ASCII code value or Unicode code point. |
| [`concat_ws(separator, string, ...)`](#::concat_wsseparator-string-) | Concatenate many strings, separated by `separator`. NULL inputs are skipped. |
| [`concat(string, ...)`](#::concatstring-) | Concatenate many strings. NULL inputs are skipped. See also [`string || string`](#::string--string). |
| [`contains(string, search_string)`](#::containsstring-search_string) | Return true if `search_string` is found within `string`. |
| [`ends_with(string, search_string)`](#::ends_withstring-search_string) | Return true if `string` ends with `search_string`. |
| [`format_bytes(bytes)`](#::format_bytesbytes) | Converts bytes to a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.). |
| [`format(format, parameters, ...)`](#::formatformat-parameters-) | Formats a string using the [fmt syntax](#::fmt-syntax). |
| [`from_base64(string)`](#::from_base64string) | Convert a base64 encoded string to a character string. |
| [`greatest(x1, x2, ...)`](#::greatestx1-x2-) | Selects the largest value using lexicographical ordering. Note that lowercase characters are considered “larger” than uppercase characters and [collations](#docs:sql:expressions:collations) are not supported. |
| [`hash(value)`](#::hashvalue) | Returns a `UBIGINT` with the hash of the `value`. |
| [`ilike_escape(string, like_specifier, escape_character)`](#::ilike_escapestring-like_specifier-escape_character) | Returns true if the `string` matches the `like_specifier` (see [Pattern Matching](#docs:sql:functions:pattern_matching)) using case-insensitive matching. `escape_character` is used to search for wildcard characters in the `string`. |
| [`instr(string, search_string)`](#::instrstring-search_string) | Return location of first occurrence of `search_string` in `string`, counting from 1. Returns 0 if no match found. |
| [`least(x1, x2, ...)`](#::leastx1-x2-) | Selects the smallest value using lexicographical ordering. Note that uppercase characters are considered “smaller” than lowercase characters, and [collations](#docs:sql:expressions:collations) are not supported. |
| [`left_grapheme(string, count)`](#::left_graphemestring-count) | Extract the left-most grapheme clusters. |
| [`left(string, count)`](#::leftstring-count) | Extract the left-most count characters. |
| [`length_grapheme(string)`](#::length_graphemestring) | Number of grapheme clusters in `string`. |
| [`length(string)`](#::lengthstring) | Number of characters in `string`. |
| [`like_escape(string, like_specifier, escape_character)`](#::like_escapestring-like_specifier-escape_character) | Returns true if the `string` matches the `like_specifier` (see [Pattern Matching](#docs:sql:functions:pattern_matching)) using case-sensitive matching. `escape_character` is used to search for wildcard characters in the `string`. |
| [`lower(string)`](#::lowerstring) | Convert `string` to lower case. |
| [`lpad(string, count, character)`](#::lpadstring-count-character) | Pads the `string`  with the character from the left until it has count characters. |
| [`ltrim(string, characters)`](#::ltrimstring-characters) | Removes any occurrences of any of the `characters` from the left side of the `string`. |
| [`ltrim(string)`](#::ltrimstring) | Removes any spaces from the left side of the `string`. |
| [`md5(string)`](#::md5string) | Returns the MD5 hash of the `string` as a `VARCHAR`. |
| [`md5_number(string)`](#::md5_numberstring) | Returns the MD5 hash of the `string` as a `HUGEINT`. |
| [`md5_number_lower(string)`](#::md5_number_lowerstring) | Returns the lower 64-bit segment of the MD5 hash of the `string` as a `BIGINT`. |
| [`md5_number_higher(string)`](#::md5_number_higherstring) | Returns the higher 64-bit segment of the MD5 hash of the `string` as a `BIGINT`. |
| [`nfc_normalize(string)`](#::nfc_normalizestring) | Convert string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not. |
| [`not_ilike_escape(string, like_specifier, escape_character)`](#::not_ilike_escapestring-like_specifier-escape_character) | Returns false if the `string` matches the `like_specifier` (see [Pattern Matching](#docs:sql:functions:pattern_matching)) using case-sensitive matching. `escape_character` is used to search for wildcard characters in the `string`. |
| [`not_like_escape(string, like_specifier, escape_character)`](#::not_like_escapestring-like_specifier-escape_character) | Returns false if the `string` matches the `like_specifier` (see [Pattern Matching](#docs:sql:functions:pattern_matching)) using case-insensitive matching. `escape_character` is used to search for wildcard characters in the `string`. |
| [`ord(string)`](#::ordstring) | Return ASCII character code of the leftmost character in a string. |
| [`parse_dirname(path, separator)`](#::parse_dirnamepath-separator) | Returns the top-level directory name from the given path. `separator` options: `system`, `both_slash` (default), `forward_slash`, `backslash`. |
| [`parse_dirpath(path, separator)`](#::parse_dirpathpath-separator) | Returns the head of the path (the pathname until the last slash) similarly to Python's [`os.path.dirname`](https://docs.python.org/3.7/library/os.path.html#os.path.dirname) function. `separator` options: `system`, `both_slash` (default), `forward_slash`, `backslash`. |
| [`parse_filename(path, trim_extension, separator)`](#::parse_filenamepath-trim_extension-separator) | Returns the last component of the path similarly to Python's [`os.path.basename`](https://docs.python.org/3.7/library/os.path.html#os.path.basename) function. If `trim_extension` is true, the file extension will be removed (defaults to `false`). `separator` options: `system`, `both_slash` (default), `forward_slash`, `backslash`. |
| [`parse_path(path, separator)`](#::parse_pathpath-separator) | Returns a list of the components (directories and filename) in the path similarly to Python's [`pathlib.parts`](https://docs.python.org/3/library/pathlib.html#pathlib.PurePath.parts) function. `separator` options: `system`, `both_slash` (default), `forward_slash`, `backslash`. |
| [`position(search_string IN string)`](#::positionsearch_string-in-string) | Return location of first occurrence of `search_string` in `string`, counting from 1. Returns 0 if no match found. |
| [`printf(format, parameters...)`](#::printfformat-parameters) | Formats a `string` using [printf syntax](#::printf-syntax). |
| [`read_text(source)`](#::read_textsource) | Returns the content from `source` (a filename, a list of filenames, or a glob pattern) as a `VARCHAR`. The file content is first validated to be valid UTF-8. If `read_text` attempts to read a file with invalid UTF-8 an error is thrown suggesting to use `read_blob` instead. See the [`read_text` guide](#docs:guides:file_formats:read_file::read_text) for more details. |
| [`regexp_escape(string)`](#::regexp_escapestring) | Escapes special patterns to turn `string` into a regular expression similarly to Python's [`re.escape` function](https://docs.python.org/3/library/re.html#re.escape). |
| [`regexp_extract(string, pattern[, group = 0])`](#regexp_extractstring-pattern-group--0) | If `string` contains the regexp `pattern`, returns the capturing group specified by optional parameter `group` (see [Pattern Matching](#docs:sql:functions:pattern_matching::using-regexp_extract)). |
| [`regexp_extract(string, pattern, name_list)`](#::regexp_extractstring-pattern-name_list) | If `string` contains the regexp `pattern`, returns the capturing groups as a struct with corresponding names from `name_list` (see [Pattern Matching](#docs:sql:functions:pattern_matching::using-regexp_extract)). |
| [`regexp_extract_all(string, regex[, group = 0])`](#regexp_extract_allstring-regex-group--0) | Split the `string` along the `regex` and extract all occurrences of `group`. |
| [`regexp_full_match(string, regex)`](#::regexp_full_matchstring-regex) | Returns `true` if the entire `string` matches the `regex` (see [Pattern Matching](#docs:sql:functions:pattern_matching)). |
| [`regexp_matches(string, pattern)`](#::regexp_matchesstring-pattern) | Returns `true` if  `string` contains the regexp `pattern`, `false` otherwise (see [Pattern Matching](#docs:sql:functions:pattern_matching::using-regexp_matches)). |
| [`regexp_replace(string, pattern, replacement)`](#::regexp_replacestring-pattern-replacement) | If `string` contains the regexp `pattern`, replaces the matching part with `replacement` (see [Pattern Matching](#docs:sql:functions:pattern_matching::using-regexp_replace)). |
| [`regexp_split_to_array(string, regex)`](#::regexp_split_to_arraystring-regex) | Splits the `string` along the `regex`. |
| [`regexp_split_to_table(string, regex)`](#::regexp_split_to_tablestring-regex) | Splits the `string` along the `regex` and returns a row for each part. |
| [`repeat(string, count)`](#::repeatstring-count) | Repeats the `string` `count` number of times. |
| [`replace(string, source, target)`](#::replacestring-source-target) | Replaces any occurrences of the `source` with `target` in `string`. |
| [`reverse(string)`](#::reversestring) | Reverses the `string`. |
| [`right_grapheme(string, count)`](#::right_graphemestring-count) | Extract the right-most `count` grapheme clusters. |
| [`right(string, count)`](#::rightstring-count) | Extract the right-most `count` characters. |
| [`rpad(string, count, character)`](#::rpadstring-count-character) | Pads the `string` with the character from the right until it has `count` characters. |
| [`rtrim(string, characters)`](#::rtrimstring-characters) | Removes any occurrences of any of the `characters` from the right side of the `string`. |
| [`rtrim(string)`](#::rtrimstring) | Removes any spaces from the right side of the `string`. |
| [`sha256(value)`](#::sha256value) | Returns a `VARCHAR` with the SHA-256 hash of the `value`. |
| [`split_part(string, separator, index)`](#::split_partstring-separator-index) | Split the `string` along the `separator` and return the data at the (1-based) `index` of the list. If the `index` is outside the bounds of the list, return an empty string (to match PostgreSQL's behavior). |
| [`starts_with(string, search_string)`](#::starts_withstring-search_string) | Return true if `string` begins with `search_string`. |
| [`str_split_regex(string, regex)`](#::str_split_regexstring-regex) | Splits the `string` along the `regex`. |
| [`string_split_regex(string, regex)`](#::string_split_regexstring-regex) | Splits the `string` along the `regex`. |
| [`string_split(string, separator)`](#::string_splitstring-separator) | Splits the `string` along the `separator`. |
| [`strip_accents(string)`](#::strip_accentsstring) | Strips accents from `string`. |
| [`strlen(string)`](#::strlenstring) | Number of bytes in `string`. |
| [`strpos(string, search_string)`](#::strposstring-search_string) | Return location of first occurrence of `search_string` in `string`, counting from 1. Returns 0 if no match found. |
| [`substring(string, start, length)`](#::substringstring-start-length) | Extract substring of `length` characters starting from character `start`. Note that a `start` value of `1` refers to the `first` character of the string. |
| [`substring_grapheme(string, start, length)`](#::substring_graphemestring-start-length) | Extract substring of `length` grapheme clusters starting from character `start`. Note that a `start` value of `1` refers to the `first` character of the string. |
| [`to_base64(blob)`](#::to_base64blob) | Convert a blob to a base64 encoded string. |
| [`trim(string, characters)`](#::trimstring-characters) | Removes any occurrences of any of the `characters` from either side of the `string`. |
| [`trim(string)`](#::trimstring) | Removes any spaces from either side of the `string`. |
| [`unicode(string)`](#::unicodestring) | Returns the Unicode code of the first character of the `string`. |
| [`upper(string)`](#::upperstring) | Convert `string` to upper case. |  
<!-- markdownlint-enable MD056 -->