# String functions

> 81 String functions in NYXDB: names, aliases, signatures and behaviour.

{/* AUTO-GENERATED from the function catalog. DO NOT EDIT — overwritten on every build. */}

81 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

| Function | Summary |
| --- | --- |
| [`append_trailing_char_if_absent`](#append_trailing_char_if_absent) | Adds a one-byte terminator only to nonempty text that does not already end with it. |
| [`ascii`](#ascii) | Returns the unsigned numeric code of a string’s leading byte, or zero for empty input. |
| [`bit_length`](#bit_length) | Measures the number of storage bits occupied by a text or binary value. |
| [`char`](#char) | Constructs one Unicode character from a scalar code or takes the first character of text. |
| [`char_length`](#char_length) | Counts Unicode code points in SQL text independently of its encoded byte size. |
| [`concat`](#concat) | Joins one or more text arguments consecutively without inserting a separator. |
| [`concat_with_separator`](#concat_with_separator) | Joins nonnull text values with a delimiter supplied as the first argument. |
| [`convert_charset`](#convert_charset) | Transcodes text bytes between explicitly named source and destination encodings. |
| [`count_matches`](#count_matches) | Counts nonoverlapping occurrences of a regular expression within source text. |
| [`count_matches_case_insensitive`](#count_matches_case_insensitive) | Tallies regex hits while treating uppercase and lowercase letters as equivalent. |
| [`edit_distance`](#edit_distance) | Computes the minimum byte insertions, deletions, and substitutions between two strings. |
| [`empty`](#empty) | Tests whether a text, binary, array, or map value has zero outer length. |
| [`ends_with`](#ends_with) | Checks whether a text value has the supplied literal suffix. |
| [`extract_all`](#extract_all) | Collects every regex match or first captured substring into a SQL array. |
| [`extract_groups`](#extract_groups) | Lists numbered captures from the earliest successful regular-expression search. |
| [`initcap`](#initcap) | Capitalizes the beginning of each ASCII word and lowercases the remaining letters. |
| [`initcap_utf8`](#initcap_utf8) | Applies initial-capital formatting to Unicode words in valid UTF-8 text. |
| [`left`](#left) | Takes a byte prefix or removes a specified number of bytes from the opposite end. |
| [`left_pad`](#left_pad) | Prepends fill bytes or truncates text to achieve a requested total byte length. |
| [`left_pad_utf8`](#left_pad_utf8) | Extends the start of UTF-8 text to a chosen character count using repeated fill. |
| [`left_utf8`](#left_utf8) | Returns leading Unicode scalars or excludes a character suffix for negative lengths. |
| [`length`](#length) | Measures bytes for text and binary inputs, elements for arrays, and entries for maps. |
| [`length_utf8`](#length_utf8) | Measures text size in Unicode scalar values rather than storage units. |
| [`like`](#like) | Matches SQL wildcard patterns where percent spans sequences and underscore spans one character. |
| [`lower`](#lower) | Converts SQL text to lowercase using the expression’s declared collation. |
| [`lower_utf8`](#lower_utf8) | Maps uppercase Unicode scalars in UTF-8 input to their lowercase forms. |
| [`match`](#match) | Tests whether an RE2 pattern matches any part of a string. |
| [`multi_search_all_positions`](#multi_search_all_positions) | Returns each literal needle’s first byte position in a shared haystack. |
| [`multi_search_any`](#multi_search_any) | Reports whether any literal from a needle array occurs in the source string. |
| [`multi_search_first_index`](#multi_search_first_index) | Identifies the earliest needle in input order that occurs anywhere in the haystack. |
| [`multi_search_first_position`](#multi_search_first_position) | Finds the smallest positive byte offset reached by any of several literal searches. |
| [`ngram_distance`](#ngram_distance) | Quantifies string dissimilarity with a normalized n-gram score between zero and one. |
| [`normalize_utf8_nfc`](#normalize_utf8_nfc) | Canonicalizes Unicode text into composed NFC form using versioned normalization tables. |
| [`normalize_utf8_nfd`](#normalize_utf8_nfd) | Expands canonically equivalent characters into decomposed NFD sequences. |
| [`normalize_utf8_nfkc`](#normalize_utf8_nfkc) | Folds compatibility characters and recomposes equivalent text into NFKC form. |
| [`normalize_utf8_nfkd`](#normalize_utf8_nfkd) | Produces compatibility-decomposed NFKD text without recombining its Unicode scalars. |
| [`not_empty`](#not_empty) | Detects at least one byte, element, or entry in supported scalar and collection values. |
| [`octet_length`](#octet_length) | Returns the encoded byte count of a string, binary value, or fixed-width string. |
| [`overlay`](#overlay) | Replaces a character-positioned section of SQL text with a supplied string. |
| [`overlay_utf8`](#overlay_utf8) | Overwrites a Unicode-scalar span beginning at a one-based text position. |
| [`position`](#position) | Locates the first literal needle in a haystack and returns a one-based SQL position. |
| [`position_case_insensitive`](#position_case_insensitive) | Searches text without letter-case distinctions and reports a one-based byte offset. |
| [`position_case_insensitive_utf8`](#position_case_insensitive_utf8) | Locates a Unicode literal irrespective of case and counts positions in scalars. |
| [`position_utf8`](#position_utf8) | Returns the character offset of a literal UTF-8 needle, optionally searching from a chosen start. |
| [`regexp_extract`](#regexp_extract) | Returns the first regex capture, or the whole match when no capture is declared. |
| [`regexp_instr`](#regexp_instr) | Locates a chosen regex occurrence or capture with character-based start and end offsets. |
| [`regexp_quote_meta`](#regexp_quote_meta) | Escapes regex metacharacters so source text can be matched literally by RE2. |
| [`regexp_replace`](#regexp_replace) | Rewrites every RE2 match using replacement text that may reference captured groups. |
| [`regexp_split_to_table`](#regexp_split_to_table) | Emits regex-delimited text fields as ordered rows with optional ordinality. |
| [`regexp_substr`](#regexp_substr) | Retrieves a selected nonoverlapping regex occurrence or capture, preserving matched empty strings. |
| [`repeat`](#repeat) | Creates a string by repeating its input a nonnegative number of times. |
| [`replace`](#replace) | Substitutes replacement text for every nonoverlapping literal substring occurrence. |
| [`replace_one`](#replace_one) | Changes only the earliest literal substring occurrence in a source string. |
| [`replace_regexp_one`](#replace_regexp_one) | Rewrites the first regex match and leaves subsequent matching spans untouched. |
| [`reverse`](#reverse) | Reorders the encoded bytes of a string from last to first. |
| [`reverse_utf8`](#reverse_utf8) | Reverses Unicode scalar order while preserving each character’s UTF-8 encoding. |
| [`right`](#right) | Returns a byte suffix or removes leading bytes when the requested length is negative. |
| [`right_pad`](#right_pad) | Appends repeated fill bytes or shortens input to a target encoded size. |
| [`right_pad_utf8`](#right_pad_utf8) | Adds trailing Unicode fill characters until text reaches a specified scalar length. |
| [`right_utf8`](#right_utf8) | Selects trailing Unicode characters, with negative counts excluding a leading prefix. |
| [`split`](#split) | Splits text on literal delimiters, preserving empty fields and an optional unsplit remainder. |
| [`split_by_alpha`](#split_by_alpha) | Separates text around runs of alphabetic bytes to expose nonalphabetic fields. |
| [`split_by_char`](#split_by_char) | Divides text at a one-byte delimiter while preserving adjacent empty fields. |
| [`split_by_non_alpha`](#split_by_non_alpha) | Extracts alphabetic byte runs by splitting source text at nonletter separators. |
| [`split_by_regexp`](#split_by_regexp) | Splits source text around matches of a delimiter pattern supplied first. |
| [`split_by_whitespace`](#split_by_whitespace) | Breaks text into fields using runs of whitespace bytes as separators. |
| [`split_part`](#split_part) | Selects one literal-delimited field by forward or backward ordinal. |
| [`starts_with`](#starts_with) | Checks a text value for a literal prefix using case-sensitive matching. |
| [`substring`](#substring) | Extracts a SQL character span using one-based FROM and optional FOR positions. |
| [`substring_index`](#substring_index) | Returns the text before or after a counted occurrence of a literal delimiter. |
| [`substring_index_utf8`](#substring_index_utf8) | Cuts UTF-8 text at a delimiter ordinal while comparing complete Unicode characters. |
| [`substring_utf8`](#substring_utf8) | Slices Unicode scalars from a one-based position or an offset counted back from the end. |
| [`to_valid_utf8`](#to_valid_utf8) | Repairs invalid UTF-8 subsequences with replacement characters while retaining valid text. |
| [`translate`](#translate) | Substitutes source bytes by positional correspondence between two character lists. |
| [`translate_utf8`](#translate_utf8) | Maps or deletes Unicode scalars according to aligned source and replacement alphabets. |
| [`trim`](#trim) | Removes selected characters from one or both ends of SQL text. |
| [`trim_left`](#trim_left) | Removes a leading run of characters drawn from an explicit set or ASCII whitespace. |
| [`trim_right`](#trim_right) | Removes trailing trim-set characters while preserving the leading and interior text. |
| [`trim_scale`](#trim_scale) | Formats exact decimals or fixed-point literals without redundant fractional zero digits. |
| [`upper`](#upper) | Changes SQL text to uppercase according to its declared collation and fixed locale rules. |
| [`upper_utf8`](#upper_utf8) | Converts lowercase UTF-8 characters to Unicode uppercase while retaining uncased scalars. |

## append_trailing_char_if_absent

Adds a one-byte terminator only to nonempty text that does not already end with it.

```text
append_trailing_char_if_absent(string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the source unchanged when it is empty or already has the requested final byte; otherwise it appends that byte once. SQL NULL in either argument yields SQL NULL. The suffix must occupy exactly one byte or INVALID_ARGUMENT is raised, and output size remains subject to RESOURCE_LIMIT.

**Example**

```sql
append_trailing_char_if_absent('sensor', '/')
-- → 'sensor/'
```

## ascii

Returns the unsigned numeric code of a string’s leading byte, or zero for empty input.

```text
ascii(string) -> uint8
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `uint8` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Reads only the first encoded byte and returns its UInt8 code, so a multibyte character produces its leading UTF-8 byte rather than its Unicode scalar value. An empty string gives zero and SQL NULL gives SQL NULL. The operation does not require decoding the remaining bytes.

**Example**

```sql
ascii('A')
-- → 65
```

## bit_length

Measures the number of storage bits occupied by a text or binary value.

```text
bit_length(value:string|bytes|fixed_string(N)) -> uint64
bit_length(string) -> uint64
bit_length(bytes) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Multiplies byte length by eight using checked UInt64 arithmetic, including all bytes of a fixed string. SQL NULL input returns SQL NULL and an empty value has zero bits. The calculation counts encoded storage rather than Unicode characters; arithmetic overflow raises INVALID_ARGUMENT.

**Example**

```sql
bit_length('abc')
-- → 24
```

## char

Constructs one Unicode character from a scalar code or takes the first character of text.

```text
char(codepoint:uint32) -> string
char(x:string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Aliases | `chr` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

The UInt32 form encodes one Unicode scalar, while the string form returns the first Unicode scalar or an empty string for empty input. SQL NULL propagates in either overload. Surrogate codes, values above U+10FFFF, or invalid UTF-8 input raise INVALID_ARGUMENT; this is not byte concatenation.

**Example**

```sql
char(233)
-- → 'é'
```

## char_length

Counts Unicode code points in SQL text independently of its encoded byte size.

```text
char_length(value:string) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `uint64` |
| Aliases | `CHARACTER_LENGTH` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the number of Unicode code points, so a multibyte encoded character counts once but a combining sequence may count more than once. SQL NULL yields SQL NULL and empty text yields zero. Invalid UTF-8 raises INVALID_ARGUMENT rather than being silently repaired.

**Example**

```sql
char_length('é')
-- → 1
```

## concat

Joins one or more text arguments consecutively without inserting a separator.

```text
concat(string,...) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Concatenates arguments in their supplied order and accepts empty strings as ordinary zero-length pieces. At least one argument is required; any SQL NULL argument makes the entire result SQL NULL. Incompatible argument types raise TYPE_MISMATCH and a result beyond the expression byte budget raises RESOURCE_LIMIT.

**Example**

```sql
concat('sen', 'sor')
-- → 'sensor'
```

## concat_with_separator

Joins nonnull text values with a delimiter supplied as the first argument.

```text
concat_with_separator(string,string,...) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic` |
| Returns | `string` |
| Aliases | `concat_ws` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL separator yields NULL; NULL values are omitted |

Inserts the separator between retained values in argument order, skipping SQL NULL values while preserving empty strings. A SQL NULL separator produces SQL NULL, and when no values remain the result is an empty string. A joined result that exceeds the byte budget raises RESOURCE_LIMIT.

**Example**

```sql
concat_with_separator('-', 'sensor', NULL, '7')
-- → 'sensor-7'
```

## convert_charset

Transcodes text bytes between explicitly named source and destination encodings.

```text
convert_charset(string,string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Interprets the source bytes using the constant source-encoding name and encodes the decoded characters using the constant destination name. SQL NULL arguments return SQL NULL, and empty input converts to empty output. Unknown encodings, invalid source sequences, or unrepresentable destination characters raise INVALID_ARGUMENT rather than being silently replaced.

**Example**

```sql
convert_charset('sensor', 'UTF-8', 'UTF-8')
-- → 'sensor'
```

## count_matches

Counts nonoverlapping occurrences of a regular expression within source text.

```text
count_matches(string,string) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `uint64` |
| Aliases | `regexp_count` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Searches with the catalog RE2 dialect and counts nonoverlapping matches instead of returning their contents. SQL NULL arguments yield SQL NULL and a pattern with no matches yields zero. Incompatible constructs or malformed patterns raise INVALID_REGEX, while compilation and input budgets can raise RESOURCE_LIMIT.

**Example**

```sql
count_matches('a12b34', '[0-9]+')
-- → 2
```

## count_matches_case_insensitive

Tallies regex hits while treating uppercase and lowercase letters as equivalent.

```text
count_matches_case_insensitive(string,string) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Counts nonoverlapping RE2 matches with case-insensitive comparison applied to the pattern. No matching span gives zero, and a SQL NULL source or pattern gives SQL NULL. Invalid regex syntax raises INVALID_REGEX; bounded compilation and scan work raise RESOURCE_LIMIT when their allowance is exceeded.

**Example**

```sql
count_matches_case_insensitive('AaA', 'a')
-- → 3
```

## edit_distance

Computes the minimum byte insertions, deletions, and substitutions between two strings.

```text
edit_distance(string,string) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns Levenshtein distance with one unit of cost for each inserted, deleted, or substituted byte, so multibyte Unicode characters may require several edits. SQL NULL propagates and comparison with an empty string costs the other string’s byte length. Working memory is local to the invocation, and excessive work or memory raises RESOURCE_LIMIT.

**Example**

```sql
edit_distance('cat', 'cut')
-- → 1
```

## empty

Tests whether a text, binary, array, or map value has zero outer length.

```text
empty(string) -> bool
empty(bytes) -> bool
empty(array(T)) -> bool
empty(map(K,V)) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `bool` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns true for a zero-byte string or binary value, an array with no elements, or a map with no entries. SQL NULL produces SQL NULL rather than true, and a container holding null elements is still nonempty. The overloaded operation inspects outer cardinality only and raises TYPE_MISMATCH for incompatible input types.

**Example**

```sql
empty('')
-- → true
```

## ends_with

Checks whether a text value has the supplied literal suffix.

```text
ends_with(string,string) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `bool` |
| Aliases | `endsWith` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Compares the requested suffix with the end of the source using literal, case-sensitive byte matching. An empty suffix matches every nonnull source, while a suffix longer than the source returns false. SQL NULL in either argument propagates SQL NULL; the suffix is not interpreted as a regular expression.

**Example**

```sql
ends_with('reading.csv', '.csv')
-- → true
```

## extract_all

Collects every regex match or first captured substring into a SQL array.

```text
extract_all(string,string) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `array(string)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Searches for all nonoverlapping RE2 matches and returns the first capture when the pattern contains one, otherwise the complete matched substrings. SQL NULL propagates, while no match produces an empty array. Invalid patterns raise INVALID_REGEX and the complete captured output must fit RESOURCE_LIMIT.

**Example**

```sql
extract_all('a12b34', '[0-9]+')
-- → ['12','34']
```

## extract_groups

Lists numbered captures from the earliest successful regular-expression search.

```text
extract_groups(string,string) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `array(string)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Finds the first RE2 match and returns its captured groups as a SQL string array in group-number order. No match returns an empty array and a SQL NULL source or pattern returns SQL NULL. Malformed expressions or incompatible regex constructs raise INVALID_REGEX; capture output is bounded by RESOURCE_LIMIT.

**Example**

```sql
extract_groups('sensor-12', '([a-z]+)-([0-9]+)')
-- → ['sensor','12']
```

## initcap

Capitalizes the beginning of each ASCII word and lowercases the remaining letters.

```text
initcap(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Applies ASCII word and letter rules, uppercasing a word’s first letter and lowercasing subsequent letters. SQL NULL stays SQL NULL and empty input stays empty; non-ASCII bytes do not receive Unicode titlecase treatment. Character conversion keeps no cross-row state and oversized output raises RESOURCE_LIMIT.

**Example**

```sql
initcap('SENSOR READING')
-- → 'Sensor Reading'
```

## initcap_utf8

Applies initial-capital formatting to Unicode words in valid UTF-8 text.

```text
initcap_utf8(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses Unicode word boundaries and case mappings to uppercase word initials and lowercase the remaining cased characters. SQL NULL propagates and uncased characters are retained. Invalid UTF-8 raises INVALID_ARGUMENT, and case-mapping output that exceeds its size budget raises RESOURCE_LIMIT.

**Example**

```sql
initcap_utf8('éCOLE SENSOR')
-- → 'École Sensor'
```

## left

Takes a byte prefix or removes a specified number of bytes from the opposite end.

```text
left(string,int64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

For a nonnegative length, returns at most that many leading bytes; a negative length omits that many bytes from the right. SQL NULL arguments return SQL NULL and requesting zero bytes returns an empty string. Byte slicing can divide a multibyte character; use the UTF-8 variant when positions must preserve Unicode scalars.

**Example**

```sql
LEFT('sensor', 3)
-- → 'sen'
```

## left_pad

Prepends fill bytes or truncates text to achieve a requested total byte length.

```text
left_pad(string,uint64) -> string
left_pad(string,uint64,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `string` |
| Aliases | `lpad` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Pads the source on the left using spaces by default or the supplied fill string, repeating fill as needed to reach the target byte size. SQL NULL propagates; a target shorter than the source truncates it, and a zero target returns empty text. Empty fill when expansion is needed raises INVALID_ARGUMENT, while excessive allocation raises RESOURCE_LIMIT.

**Example**

```sql
left_pad('7', 3, '0')
-- → '007'
```

## left_pad_utf8

Extends the start of UTF-8 text to a chosen character count using repeated fill.

```text
left_pad_utf8(string,uint64) -> string
left_pad_utf8(string,uint64,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Measures source, target, and fill in Unicode scalar values, prepending spaces by default and truncating when the target is shorter. SQL NULL arguments yield SQL NULL and a zero target gives an empty string. Invalid UTF-8 or an unusable empty fill raises INVALID_ARGUMENT; the rendered byte size must fit RESOURCE_LIMIT.

**Example**

```sql
left_pad_utf8('é', 3, '0')
-- → '00é'
```

## left_utf8

Returns leading Unicode scalars or excludes a character suffix for negative lengths.

```text
left_utf8(string,int64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Takes at most the requested number of Unicode scalar values from the beginning, or removes a suffix of the stated magnitude when length is negative. SQL NULL propagates and zero length returns empty text. Invalid UTF-8 raises INVALID_ARGUMENT, and scalar boundaries are preserved without claiming grapheme-cluster slicing.

**Example**

```sql
left_utf8('éclair', 2)
-- → 'éc'
```

## length

Measures bytes for text and binary inputs, elements for arrays, and entries for maps.

```text
length(array(T)) -> uint64
length(string) -> uint64
length(bytes) -> uint64
length(fixed_string(N)) -> uint64
length(map(K,V)) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns encoded byte length for strings, bytes, and fixed strings, or outer cardinality for arrays and maps. SQL NULL produces SQL NULL and empty values return zero; nested container contents do not affect outer cardinality. Incompatible types raise TYPE_MISMATCH, and text byte length does not imply Unicode character count.

**Example**

```sql
length('é')
-- → 2
```

## length_utf8

Measures text size in Unicode scalar values rather than storage units.

```text
length_utf8(string) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `uint64` |
| Aliases | `lengthUTF8` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Decodes valid UTF-8 and counts Unicode scalars, so combining marks count independently and multibyte encodings count once per scalar. Empty input gives zero and SQL NULL gives SQL NULL. Invalid byte sequences raise INVALID_ARGUMENT; this operation does not count visual grapheme clusters.

**Example**

```sql
length_utf8('éclair')
-- → 6
```

## like

Matches SQL wildcard patterns where percent spans sequences and underscore spans one character.

```text
like(value:string,pattern:string) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `bool` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Matches the entire source against a pattern whose percent wildcard consumes any sequence and whose underscore consumes one UTF-8 code point. SQL NULL source or pattern produces SQL NULL, and this callable has no escape argument. Other pattern characters are literal, and invalid character input raises INVALID_ARGUMENT.

**Example**

```sql
LIKE('sensor7', 'sensor_')
-- → true
```

## lower

Converts SQL text to lowercase using the expression’s declared collation.

```text
lower(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Applies SQL lowercase conversion under the declared collation, without consulting a session-dependent locale. SQL NULL remains SQL NULL and the empty string remains empty. Characters without a lowercase mapping are retained; invalid character input raises INVALID_ARGUMENT.

**Example**

```sql
lower('SENSOR')
-- → 'sensor'
```

## lower_utf8

Maps uppercase Unicode scalars in UTF-8 input to their lowercase forms.

```text
lower_utf8(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Aliases | `lowerUTF8` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Decodes the source as Unicode and applies lowercase mappings while preserving characters without a lowercase counterpart. SQL NULL returns SQL NULL and empty text remains empty. Invalid UTF-8 raises INVALID_ARGUMENT, and the transformed byte string must remain within RESOURCE_LIMIT.

**Example**

```sql
lower_utf8('ÉCOLE')
-- → 'école'
```

## match

Tests whether an RE2 pattern matches any part of a string.

```text
match(string,string) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `bool` |
| Aliases | `regexp_like` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Performs partial regex matching, so explicit anchors are required when the pattern must cover the entire source. A SQL NULL argument propagates SQL NULL, and failure to find any match returns false. Malformed syntax, lookbehind, and pattern backreferences raise INVALID_REGEX; admitted regex work is bounded by RESOURCE_LIMIT.

**Example**

```sql
match('sensor-12', '[0-9]+')
-- → true
```

## multi_search_all_positions

Returns each literal needle’s first byte position in a shared haystack.

```text
multi_search_all_positions(string,array(string)) -> array(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `array(uint64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Produces one UInt64 position for each needle in the supplied array, preserving needle order and using one-based byte positions. An absent needle receives zero and an empty needle array produces an empty result array. SQL NULL arguments propagate, and excessive combined search work or output raises RESOURCE_LIMIT.

**Example**

```sql
multi_search_all_positions('sensor', ['sen', 'or', 'x'])
-- → [1,5,0]
```

## multi_search_any

Reports whether any literal from a needle array occurs in the source string.

```text
multi_search_any(string,array(string)) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `bool` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Searches the haystack for at least one supplied needle using literal byte matching, without treating needles as regular expressions. An empty needle collection returns false, while SQL NULL input returns SQL NULL. Search work is local to each invocation and exceeds its admitted budget only by raising RESOURCE_LIMIT.

**Example**

```sql
multi_search_any('sensor', ['x', 'or'])
-- → true
```

## multi_search_first_index

Identifies the earliest needle in input order that occurs anywhere in the haystack.

```text
multi_search_first_index(string,array(string)) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the one-based index within the needle array of the first needle that matches, rather than the earliest match position in the text. No matching needle or an empty needle array returns zero, and SQL NULL arguments propagate. Literal searches obey the expression work budget and raise RESOURCE_LIMIT on excess.

**Example**

```sql
multi_search_first_index('sensor', ['or', 'sen'])
-- → 1
```

## multi_search_first_position

Finds the smallest positive byte offset reached by any of several literal searches.

```text
multi_search_first_position(string,array(string)) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Computes the earliest source position matched by any needle, comparing one-based byte offsets rather than needle-array indices. If all needles are absent or the needle array is empty, the result is zero. SQL NULL propagates, and excessive aggregate needle-search work raises RESOURCE_LIMIT.

**Example**

```sql
multi_search_first_position('sensor', ['or', 'sen'])
-- → 1
```

## ngram_distance

Quantifies string dissimilarity with a normalized n-gram score between zero and one.

```text
ngram_distance(string,string) -> float32
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `float32` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Compares the n-gram representations of the two strings and returns a Float32 distance constrained to the interval from zero to one. SQL NULL arguments yield SQL NULL, and identical inputs have distance zero. The operation keeps only invocation-local work data and raises RESOURCE_LIMIT if its comparison budget is exceeded.

**Example**

```sql
ngram_distance('sensor', 'sensor')
-- → 0
```

## normalize_utf8_nfc

Canonicalizes Unicode text into composed NFC form using versioned normalization tables.

```text
normalize_utf8_nfc(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Applies canonical decomposition followed by canonical composition, combining eligible base-and-mark sequences without compatibility folding. SQL NULL propagates and already normalized text is unchanged. Invalid UTF-8 raises INVALID_ARGUMENT; Unicode table versions are fixed for deterministic replay and output growth is bounded by RESOURCE_LIMIT.

**Example**

```sql
normalize_utf8_nfc('é')
-- → 'é'
```

## normalize_utf8_nfd

Expands canonically equivalent characters into decomposed NFD sequences.

```text
normalize_utf8_nfd(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Performs canonical decomposition and combining-mark ordering without recomposing the result or folding compatibility characters. SQL NULL produces SQL NULL, while an empty string remains empty. INVALID_ARGUMENT rejects malformed UTF-8; versioned Unicode tables fix replay semantics and RESOURCE_LIMIT bounds expanded output.

**Example**

```sql
normalize_utf8_nfd('é')
-- → 'é'
```

## normalize_utf8_nfkc

Folds compatibility characters and recomposes equivalent text into NFKC form.

```text
normalize_utf8_nfkc(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses compatibility decomposition followed by canonical composition, allowing presentation variants such as full-width characters to normalize to ordinary equivalents. SQL NULL propagates, and this transformation may intentionally discard compatibility distinctions. Invalid UTF-8 raises INVALID_ARGUMENT; versioned mappings and RESOURCE_LIMIT make normalization reproducible and bounded.

**Example**

```sql
normalize_utf8_nfkc('Ａ')
-- → 'A'
```

## normalize_utf8_nfkd

Produces compatibility-decomposed NFKD text without recombining its Unicode scalars.

```text
normalize_utf8_nfkd(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Expands both canonical and compatibility equivalents and orders combining marks, leaving the resulting sequence decomposed. SQL NULL remains SQL NULL and compatibility distinctions may be lost even when the source looks unchanged visually. Invalid encoding raises INVALID_ARGUMENT; table versions govern replay and RESOURCE_LIMIT bounds expansion.

**Example**

```sql
normalize_utf8_nfkd('fi')
-- → 'fi'
```

## not_empty

Detects at least one byte, element, or entry in supported scalar and collection values.

```text
not_empty(string) -> bool
not_empty(bytes) -> bool
not_empty(array(T)) -> bool
not_empty(map(K,V)) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `bool` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns false for zero-length strings or binary values and for arrays or maps with zero outer cardinality. SQL NULL returns SQL NULL, while an array containing a null element still returns true. The operation does not examine nested values and rejects incompatible input types with TYPE_MISMATCH.

**Example**

```sql
not_empty('sensor')
-- → true
```

## octet_length

Returns the encoded byte count of a string, binary value, or fixed-width string.

```text
octet_length(value:string|bytes|fixed_string(N)) -> uint64
octet_length(string) -> uint64
octet_length(bytes) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Measures stored bytes rather than displayed characters, counting every byte of a multibyte UTF-8 sequence and every fixed-string position. SQL NULL propagates and an empty variable-length value returns zero. Binary values are measured without character decoding, so their contents need not form valid UTF-8.

**Example**

```sql
octet_length('é')
-- → 2
```

## overlay

Replaces a character-positioned section of SQL text with a supplied string.

```text
overlay(string,string,int64) -> string
overlay(string,string,int64,uint64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses one-based character positions with ANSI PLACING, FROM, and optional FOR syntax; without FOR, the replacement span follows the replacement text length. A SQL NULL source, replacement, position, or length produces SQL NULL. Invalid positions or character encoding raise INVALID_ARGUMENT, and excessive result size raises RESOURCE_LIMIT.

**Example**

```sql
overlay('sensor', 'X', 2, 2)
-- → 'sXsor'
```

## overlay_utf8

Overwrites a Unicode-scalar span beginning at a one-based text position.

```text
overlay_utf8(string,string,int64) -> string
overlay_utf8(string,string,int64,uint64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/3..4-ary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Replaces the requested source span using Unicode scalar positions, with an optional explicit replacement length. SQL NULL in any supplied argument yields SQL NULL, and replacement preserves complete scalar boundaries. Invalid UTF-8 or an invalid position raises INVALID_ARGUMENT; output beyond the expression byte budget raises RESOURCE_LIMIT.

**Example**

```sql
overlay_utf8('éclair', 'X', 2, 2)
-- → 'éXair'
```

## position

Locates the first literal needle in a haystack and returns a one-based SQL position.

```text
position(string,string) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `uint64` |
| Aliases | `strpos` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

ANSI position(needle IN haystack) returns the first character position or zero when the needle is absent. SQL NULL input propagates, and an empty needle is found at the first position. Haystack-first and strpos ingress forms are normalized explicitly, preventing an alias from silently reversing the search.

**Example**

```sql
position('sen' IN 'sensor')
-- → 1
```

## position_case_insensitive

Searches text without letter-case distinctions and reports a one-based byte offset.

```text
position_case_insensitive(string,string) -> uint64
position_case_insensitive(string,string,uint64) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses haystack-first argument order and an optional starting byte position, returning the first case-insensitive literal match. Absence produces zero and SQL NULL arguments produce SQL NULL. The result counts encoded bytes rather than Unicode scalars, and an invalid start raises INVALID_ARGUMENT.

**Example**

```sql
position_case_insensitive('Sensor', 'SEN')
-- → 1
```

## position_case_insensitive_utf8

Locates a Unicode literal irrespective of case and counts positions in scalars.

```text
position_case_insensitive_utf8(string,string) -> uint64
position_case_insensitive_utf8(string,string,uint64) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Searches the UTF-8 haystack from an optional one-based scalar offset using case-insensitive comparison. It returns zero when the needle is absent and SQL NULL when any argument is SQL NULL. Invalid UTF-8 or an invalid starting offset raises INVALID_ARGUMENT, and positions never count individual encoding bytes.

**Example**

```sql
position_case_insensitive_utf8('éSensor', 'SEN')
-- → 2
```

## position_utf8

Returns the character offset of a literal UTF-8 needle, optionally searching from a chosen start.

```text
position_utf8(string,string) -> uint64
position_utf8(string,string,uint64) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Performs a case-sensitive haystack-first search and reports the first match as a one-based Unicode scalar position. Missing needles return zero and SQL NULL arguments propagate SQL NULL. Both the optional start and the result count scalars, with INVALID_ARGUMENT for invalid UTF-8 or an invalid starting position.

**Example**

```sql
position_utf8('éSensor', 'Sen')
-- → 2
```

## regexp_extract

Returns the first regex capture, or the whole match when no capture is declared.

```text
regexp_extract(value:string,pattern:string) -> string
regexp_extract(value:string,pattern:string,group:uint32) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

The two-argument form selects the first capture when present; an explicit group of zero selects the whole first match. No match or a nonparticipating optional capture gives an empty string, and SQL NULL arguments propagate. Invalid patterns raise INVALID_REGEX, nonexistent capture indices raise INVALID_ARGUMENT, and regex work is subject to RESOURCE_LIMIT.

**Example**

```sql
regexp_extract('sensor-12', 'sensor-([0-9]+)')
-- → '12'
```

## regexp_instr

Locates a chosen regex occurrence or capture with character-based start and end offsets.

```text
regexp_instr(value:string,pattern:string) -> uint64
regexp_instr(value:string,pattern:string,start:uint64,occurrence:uint64,end_position:bool,flags:string,group:uint32) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..7-ary` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns a one-based Unicode scalar position, or the position immediately after the selected span when end_position is true; short form selects the first whole match from position one. No match or a nonparticipating group yields zero, SQL NULL propagates, and zero-length matches advance by one scalar between searches. INVALID_ARGUMENT rejects nonpositive offsets, occurrences, unknown or contradictory i/m/s/c flags, and missing capture indices; INVALID_REGEX rejects invalid RE2 patterns.

**Example**

```sql
regexp_instr('sensor-12', '[0-9]+')
-- → 8
```

## regexp_quote_meta

Escapes regex metacharacters so source text can be matched literally by RE2.

```text
regexp_quote_meta(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Adds the required quoting before characters that have special meaning in an RE2 pattern while preserving ordinary characters. SQL NULL produces SQL NULL and empty input produces an empty pattern string. This operation quotes text without compiling it, and output growth beyond the expression limit raises RESOURCE_LIMIT.

**Example**

```sql
regexp_quote_meta('sensor')
-- → 'sensor'
```

## regexp_replace

Rewrites every RE2 match using replacement text that may reference captured groups.

```text
replace_regexp_all(string,string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Aliases | `replace_regex`, `replaceRegexpAll`, `replace_regexp_all` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Applies global RE2 replacement across nonoverlapping matches, with capture references interpreted in the replacement expression. SQL NULL arguments return SQL NULL and a source with no matches is unchanged. Invalid pattern or replacement-reference syntax raises INVALID_REGEX, and compilation or expanded-output limits raise RESOURCE_LIMIT.

**Example**

```sql
replace_regexp_all('a12b34', '[0-9]+', '#')
-- → 'a#b#'
```

## regexp_split_to_table

Emits regex-delimited text fields as ordered rows with optional ordinality.

```text
regexp_split_to_table(value:string,pattern:string) -> table(tuple(value:string))
regexp_split_to_table(value:string,pattern:string,flags:string) -> table(tuple(value:string))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/2..3-ary` |
| Returns | `table(tuple(value:string))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL argument emits zero rows |

Retains empty fields beside nonempty delimiters, emits one empty field for empty source, and ignores zero-width matches at endpoints or immediately after the preceding delimiter. SQL NULL arguments emit no rows; unknown or contradictory i/m/s/c flags raise INVALID_ARGUMENT and invalid RE2 patterns raise INVALID_REGEX. Optional ordinality starts at one, and independent per-input expansion raises RESOURCE_LIMIT when row or byte bounds are exceeded.

**Example**

```sql
SELECT *
FROM regexp_split_to_table('a,b;c', '[,;]')
-- → [('a'),('b'),('c')]
```

## regexp_substr

Retrieves a selected nonoverlapping regex occurrence or capture, preserving matched empty strings.

```text
regexp_substr(value:string,pattern:string) -> nullable(string)
regexp_substr(value:string,pattern:string,start:uint64,occurrence:uint64,flags:string,group:uint32) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..6-ary` |
| Returns | `nullable(string)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

The short form returns the first complete RE2 match; the extended form chooses a positive scalar start, occurrence, flags, and capture group, with group zero selecting the whole match. SQL NULL, no match, or a nonparticipating capture yields SQL NULL, whereas a matched empty substring stays empty; zero-width matches advance one scalar before another search. Invalid groups, offsets, occurrences, or i/m/s/c flags raise INVALID_ARGUMENT and malformed patterns raise INVALID_REGEX.

**Example**

```sql
regexp_substr('sensor-12', '[0-9]+')
-- → '12'
```

## repeat

Creates a string by repeating its input a nonnegative number of times.

```text
repeat(string,uint64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Concatenates exactly the requested number of copies in order, producing an empty string for zero repetitions or an empty source. A SQL NULL source or count propagates SQL NULL. Count-type violations raise TYPE_MISMATCH, and checked output-size admission raises RESOURCE_LIMIT before an excessive allocation.

**Example**

```sql
REPEAT('ab', 3)
-- → 'ababab'
```

## replace

Substitutes replacement text for every nonoverlapping literal substring occurrence.

```text
replace(string,string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Aliases | `replace_all`, `replaceAll` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Scans the source for literal search-string occurrences and replaces every nonoverlapping match without interpreting regex syntax. A missing search string leaves the source unchanged, and SQL NULL in any argument returns SQL NULL. Replacement output is bounded by the expression byte budget and raises RESOURCE_LIMIT when too large.

**Example**

```sql
REPLACE('a-b-a', 'a', 'x')
-- → 'x-b-x'
```

## replace_one

Changes only the earliest literal substring occurrence in a source string.

```text
replace_one(string,string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Finds the first case-sensitive literal occurrence and substitutes the replacement once, retaining any later occurrences unchanged. If the search text is absent, the original source is returned; SQL NULL arguments propagate. The resulting byte string must fit the admitted output budget or RESOURCE_LIMIT is raised.

**Example**

```sql
replace_one('a-b-a', 'a', 'x')
-- → 'x-b-a'
```

## replace_regexp_one

Rewrites the first regex match and leaves subsequent matching spans untouched.

```text
replace_regexp_one(string,string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Aliases | `replaceRegexpOne` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses RE2 to find one initial match and expands supported capture references in the replacement for that span only. If no match exists, the source remains unchanged; SQL NULL in any argument yields SQL NULL. Malformed regex or capture-reference syntax raises INVALID_REGEX and excessive output raises RESOURCE_LIMIT.

**Example**

```sql
replace_regexp_one('a12b34', '[0-9]+', '#')
-- → 'a#b34'
```

## reverse

Reorders the encoded bytes of a string from last to first.

```text
reverse(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Reverses bytes without decoding characters, so a multibyte UTF-8 input may produce invalid UTF-8 after reversal. SQL NULL returns SQL NULL, and empty or one-byte input is unchanged. Use the Unicode variant when each scalar encoding must remain intact; the byte-oriented operation preserves source length.

**Example**

```sql
reverse('sensor')
-- → 'rosnes'
```

## reverse_utf8

Reverses Unicode scalar order while preserving each character’s UTF-8 encoding.

```text
reverse_utf8(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Decodes valid UTF-8 and emits its Unicode scalars in reverse order, without reversing the bytes within each scalar. SQL NULL propagates and empty input remains empty. Invalid encoding raises INVALID_ARGUMENT; combining sequences are not treated as indivisible grapheme clusters.

**Example**

```sql
reverse_utf8('éa')
-- → 'aé'
```

## right

Returns a byte suffix or removes leading bytes when the requested length is negative.

```text
right(string,int64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

For a nonnegative count, takes at most that many final bytes; a negative count excludes that many bytes from the left. SQL NULL input propagates and a zero count gives an empty string. Because positions count bytes, the result can split a multibyte character at its left boundary.

**Example**

```sql
RIGHT('sensor', 3)
-- → 'sor'
```

## right_pad

Appends repeated fill bytes or shortens input to a target encoded size.

```text
right_pad(string,uint64) -> string
right_pad(string,uint64,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `string` |
| Aliases | `rpad` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Pads on the right with spaces by default or a supplied repeated fill string, measuring the target in bytes. SQL NULL propagates, zero target length yields empty text, and a shorter target truncates the source. An empty fill that cannot satisfy expansion raises INVALID_ARGUMENT; excessive requested size raises RESOURCE_LIMIT.

**Example**

```sql
right_pad('7', 3, '0')
-- → '700'
```

## right_pad_utf8

Adds trailing Unicode fill characters until text reaches a specified scalar length.

```text
right_pad_utf8(string,uint64) -> string
right_pad_utf8(string,uint64,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Counts Unicode scalars when padding or truncating, using spaces if no fill is supplied and preserving complete UTF-8 sequences. SQL NULL arguments return SQL NULL and target zero returns an empty string. Invalid encoding or empty fill during required expansion raises INVALID_ARGUMENT, while byte-budget excess raises RESOURCE_LIMIT.

**Example**

```sql
right_pad_utf8('é', 3, '.')
-- → 'é..'
```

## right_utf8

Selects trailing Unicode characters, with negative counts excluding a leading prefix.

```text
right_utf8(string,int64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

A nonnegative length takes at most that many final Unicode scalars; a negative length removes the corresponding number from the beginning. SQL NULL propagates and a zero length gives empty text. Invalid UTF-8 raises INVALID_ARGUMENT, and counting scalars does not combine grapheme clusters.

**Example**

```sql
right_utf8('café', 2)
-- → 'fé'
```

## split

Splits text on literal delimiters, preserving empty fields and an optional unsplit remainder.

```text
split(string,string) -> array(string)
split(string,string,uint64) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `array(string)` |
| Aliases | `split_by_string`, `splitByString`, `string_to_array` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses delimiter-first order and preserves empty fields between adjacent delimiters; the delimiter may contain multiple bytes, while an empty delimiter splits UTF-8 code points. SQL NULL propagates, and an optional positive field limit leaves the unsplit remainder as the last field. Invalid limits or invalid UTF-8 during empty-delimiter splitting raise INVALID_ARGUMENT, and excessive output raises RESOURCE_LIMIT.

**Example**

```sql
split('::', 'a::::b')
-- → ['a','','b']
```

## split_by_alpha

Separates text around runs of alphabetic bytes to expose nonalphabetic fields.

```text
split_by_alpha(string) -> array(string)
split_by_alpha(string,uint64) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `array(string)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Treats runs of alphabetic bytes as separators and returns the remaining fields in source order, optionally limiting the number of substrings. SQL NULL source or limit returns SQL NULL. Classification is byte-oriented rather than Unicode-word based, and the emitted array must fit RESOURCE_LIMIT.

**Example**

```sql
split_by_alpha('12ab34')
-- → ['12','34']
```

## split_by_char

Divides text at a one-byte delimiter while preserving adjacent empty fields.

```text
split_by_char(string,string) -> array(string)
split_by_char(string,string,uint64) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `array(string)` |
| Aliases | `splitByChar` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Takes the delimiter first and the source second, splitting literal occurrences in order and retaining empty fields between adjacent separators. SQL NULL arguments propagate, and the optional limit bounds the number of substrings. A delimiter whose encoded length is not exactly one byte raises INVALID_ARGUMENT; result growth is bounded by RESOURCE_LIMIT.

**Example**

```sql
split_by_char(',', 'a,,b')
-- → ['a','','b']
```

## split_by_non_alpha

Extracts alphabetic byte runs by splitting source text at nonletter separators.

```text
split_by_non_alpha(string) -> array(string)
split_by_non_alpha(string,uint64) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `array(string)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses runs of nonalphabetic bytes as delimiters and returns source-order fields, with an optional maximum substring count. SQL NULL arguments return SQL NULL and ASCII-style byte classification does not infer Unicode word boundaries. Array output and scanning work are admitted per input and can raise RESOURCE_LIMIT.

**Example**

```sql
split_by_non_alpha('sensor12reading')
-- → ['sensor','reading']
```

## split_by_regexp

Splits source text around matches of a delimiter pattern supplied first.

```text
split_by_regexp(string,string) -> array(string)
split_by_regexp(string,string,uint64) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `array(string)` |
| Aliases | `regexp_split_to_array`, `splitByRegexp` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Interprets the first argument as an RE2 delimiter pattern and the second as source text, with an optional maximum number of substrings. SQL NULL arguments produce SQL NULL; source-first legacy calls require explicit ingress reordering. Malformed or incompatible patterns raise INVALID_REGEX and expanded field output can raise RESOURCE_LIMIT.

**Example**

```sql
split_by_regexp('[,;]', 'a,b;c')
-- → ['a','b','c']
```

## split_by_whitespace

Breaks text into fields using runs of whitespace bytes as separators.

```text
split_by_whitespace(string) -> array(string)
split_by_whitespace(string,uint64) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `array(string)` |
| Aliases | `splitByWhitespace` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Finds whitespace-delimited fields in source order and optionally restricts the number of returned substrings. SQL NULL source or optional limit propagates SQL NULL, and separator classification follows the byte-oriented whitespace contract. Per-input output cardinality and bytes are bounded and raise RESOURCE_LIMIT when exceeded.

**Example**

```sql
split_by_whitespace('sensor reading')
-- → ['sensor','reading']
```

## split_part

Selects one literal-delimited field by forward or backward ordinal.

```text
split_part(value:string,delimiter:string,field:int64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Positive field numbers count from one and negative numbers count backward from the final field; a missing field returns an empty string. Adjacent delimiters retain empty fields, an empty delimiter treats the whole source as one field, and SQL NULL arguments propagate. Field zero raises INVALID_ARGUMENT; delimiter matching is literal UTF-8 text.

**Example**

```sql
split_part('sensor:reading:unit', ':', -1)
-- → 'unit'
```

## starts_with

Checks a text value for a literal prefix using case-sensitive matching.

```text
starts_with(string,string) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `bool` |
| Aliases | `startsWith` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Compares the requested prefix against the beginning of the source without regex interpretation or locale-dependent folding. An empty prefix is true for every nonnull string, while a longer-than-source prefix is false. SQL NULL source or prefix produces SQL NULL.

**Example**

```sql
starts_with('sensor-7', 'sensor')
-- → true
```

## substring

Extracts a SQL character span using one-based FROM and optional FOR positions.

```text
substring(string,int64) -> string
substring(string,int64,uint64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `string` |
| Aliases | `substr` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses SQL character positions with a one-based start and optional length, accepting both FROM/FOR syntax and normalized comma syntax. SQL NULL supplied arguments return SQL NULL and a zero length returns an empty string. Byte-oriented compatibility requires an explicit mode, while invalid character input or incompatible positions raise INVALID_ARGUMENT.

**Example**

```sql
substring('sensor' FROM 2 FOR 3)
-- → 'ens'
```

## substring_index

Returns the text before or after a counted occurrence of a literal delimiter.

```text
substring_index(string,string,int64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

A positive count retains the prefix before that many delimiters from the start, while a negative count retains the suffix after counting backward from the end. SQL NULL arguments return SQL NULL, zero count returns an empty string, and too few delimiters leave the full source. This variant performs byte-oriented delimiter comparisons.

**Example**

```sql
substring_index('a:b:c', ':', 2)
-- → 'a:b'
```

## substring_index_utf8

Cuts UTF-8 text at a delimiter ordinal while comparing complete Unicode characters.

```text
substring_index_utf8(string,string,int64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Counts delimiter occurrences from the beginning for a positive count or from the end for a negative count, returning the corresponding prefix or suffix. SQL NULL propagates, zero returns empty text, and a count beyond available delimiters returns the original string. Comparisons respect complete code points and invalid UTF-8 raises INVALID_ARGUMENT.

**Example**

```sql
substring_index_utf8('é:a:b', ':', -2)
-- → 'a:b'
```

## substring_utf8

Slices Unicode scalars from a one-based position or an offset counted back from the end.

```text
substring_utf8(string,int64) -> string
substring_utf8(string,int64,uint64) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `string` |
| Aliases | `substringUTF8` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the requested scalar span, or the remaining suffix when length is omitted; a negative start counts from the end. SQL NULL supplied arguments produce SQL NULL, and a zero requested length produces an empty string. Invalid UTF-8 or an invalid start raises INVALID_ARGUMENT, and slicing preserves scalar boundaries rather than grapheme clusters.

**Example**

```sql
substring_utf8('éclair', 2, 3)
-- → 'cla'
```

## to_valid_utf8

Repairs invalid UTF-8 subsequences with replacement characters while retaining valid text.

```text
to_valid_utf8(x:string|bytes) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Copies valid Unicode scalar sequences and replaces each maximal invalid UTF-8 subsequence with one U+FFFD replacement character. SQL NULL yields SQL NULL, and already valid input is preserved byte for byte. Both string and bytes inputs are accepted; repaired output beyond the expression byte budget raises RESOURCE_LIMIT.

**Example**

```sql
to_valid_utf8('sensor')
-- → 'sensor'
```

## translate

Substitutes source bytes by positional correspondence between two character lists.

```text
translate(string,string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Maps source bytes listed in from_characters to the corresponding positions in to_characters, leaving unlisted bytes unchanged. If the target list has no corresponding position, the source byte is deleted; SQL NULL in any argument propagates. Byte-based substitution can split multibyte encodings, so Unicode scalar substitution uses the UTF-8 variant.

**Example**

```sql
translate('abc', 'ac', 'X')
-- → 'Xb'
```

## translate_utf8

Maps or deletes Unicode scalars according to aligned source and replacement alphabets.

```text
translate_utf8(string,string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Matches complete Unicode scalars against from_characters and substitutes their positional counterparts in to_characters. Missing target positions delete matching source scalars, unmapped scalars remain intact, and SQL NULL arguments return SQL NULL. Invalid UTF-8 in any argument raises INVALID_ARGUMENT; output growth is subject to RESOURCE_LIMIT.

**Example**

```sql
translate_utf8('éa', 'é', 'E')
-- → 'Ea'
```

## trim

Removes selected characters from one or both ends of SQL text.

```text
trim(string) -> string
trim(string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `string` |
| Aliases | `btrim`, `trim_both` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

SQL syntax supports BOTH, LEADING, or TRAILING with a trim character; the comma form retains character-set trimming semantics. SQL NULL arguments yield SQL NULL, and a value consisting only of trimmed characters becomes empty. Interior characters remain untouched, and an empty character set in the comma form removes nothing.

**Example**

```sql
trim('  sensor  ')
-- → 'sensor'
```

## trim_left

Removes a leading run of characters drawn from an explicit set or ASCII whitespace.

```text
trim_left(string) -> string
trim_left(string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `string` |
| Aliases | `ltrim` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Strips characters only from the beginning until encountering a character outside the trim set, leaving matching characters inside or at the end untouched. Omitting the set selects ASCII whitespace; the supplied set is a collection of characters rather than a substring. SQL NULL arguments propagate, and a source made entirely of trim characters becomes empty.

**Example**

```sql
trim_left('  sensor  ')
-- → 'sensor  '
```

## trim_right

Removes trailing trim-set characters while preserving the leading and interior text.

```text
trim_right(string) -> string
trim_right(string,string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `string` |
| Aliases | `rtrim` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Scans backward from the end and removes characters belonging to the specified set, defaulting to ASCII whitespace. SQL NULL input or trim set yields SQL NULL, and trimming an all-matching source yields an empty string. A multicharacter trim set supplies alternatives; it is not matched as one suffix substring.

**Example**

```sql
trim_right('  sensor  ')
-- → '  sensor'
```

## trim_scale

Formats exact decimals or fixed-point literals without redundant fractional zero digits.

```text
trim_scale(value:string) -> string
trim_scale(value:decimal(P,S)) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Removes only trailing fractional zeros and any decimal point left with no fractional digits, preserving integer digits and an explicit sign. SQL NULL returns SQL NULL; decimal values are rendered from their exact coefficient and scale, and both overloads return string. Text containing whitespace, exponent notation, missing required digits, or nonnumeric content raises INVALID_ARGUMENT.

**Example**

```sql
trim_scale('+12.3400')
-- → '+12.34'
```

## upper

Changes SQL text to uppercase according to its declared collation and fixed locale rules.

```text
upper(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Uses the expression’s declared collation for uppercase conversion without depending on a session locale. SQL NULL produces SQL NULL, empty input remains empty, and characters with no uppercase mapping are retained. Invalid character data raises INVALID_ARGUMENT; changed encodings remain subject to the expression output limit.

**Example**

```sql
upper('sensor')
-- → 'SENSOR'
```

## upper_utf8

Converts lowercase UTF-8 characters to Unicode uppercase while retaining uncased scalars.

```text
upper_utf8(string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary` |
| Returns | `string` |
| Aliases | `upperUTF8` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Applies Unicode uppercase mappings to decoded scalar values and retains characters with no uppercase mapping. SQL NULL propagates and the empty string remains empty. Invalid UTF-8 raises INVALID_ARGUMENT, and the case-converted output must fit the expression byte limit or RESOURCE_LIMIT is raised.

**Example**

```sql
upper_utf8('école')
-- → 'ÉCOLE'
```

---

Source: https://nyxdb.io/docs/functions/string/