String functions

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.

append_trailing_char_if_absent

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

append_trailing_char_if_absent(string,string) -> string

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

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

ascii

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

ascii(string) -> uint8

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

ascii('A')
-- → 65

bit_length

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

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

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

bit_length('abc')
-- → 24

char

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

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

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

char(233)
-- → 'é'

char_length

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

char_length(value:string) -> uint64

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

char_length('é')
-- → 1

concat

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

concat(string,...) -> string

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

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

concat_with_separator

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

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

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

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

convert_charset

Transcodes text bytes between explicitly named source and destination encodings.

convert_charset(string,string,string) -> string

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

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

count_matches

Counts nonoverlapping occurrences of a regular expression within source text.

count_matches(string,string) -> uint64

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

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

count_matches_case_insensitive

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

count_matches_case_insensitive(string,string) -> uint64

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

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

edit_distance

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

edit_distance(string,string) -> uint64

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

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

empty

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

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

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

empty('')
-- → true

ends_with

Checks whether a text value has the supplied literal suffix.

ends_with(string,string) -> bool

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

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

extract_all

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

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

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

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

extract_groups

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

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

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

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

initcap

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

initcap(string) -> string

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

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

initcap_utf8

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

initcap_utf8(string) -> string

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

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

left

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

left(string,int64) -> string

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

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

left_pad

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

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

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

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

left_pad_utf8

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

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

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

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

left_utf8

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

left_utf8(string,int64) -> string

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

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

length

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

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

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

length('é')
-- → 2

length_utf8

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

length_utf8(string) -> uint64

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

length_utf8('éclair')
-- → 6

like

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

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

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

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

lower

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

lower(string) -> string

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

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

lower_utf8

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

lower_utf8(string) -> string

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

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

match

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

match(string,string) -> bool

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

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

multi_search_all_positions

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

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

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

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.

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

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

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

multi_search_first_index

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

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

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

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.

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

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

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

ngram_distance

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

ngram_distance(string,string) -> float32

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

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

normalize_utf8_nfc

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

normalize_utf8_nfc(string) -> string

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

normalize_utf8_nfc('é')
-- → 'é'

normalize_utf8_nfd

Expands canonically equivalent characters into decomposed NFD sequences.

normalize_utf8_nfd(string) -> string

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

normalize_utf8_nfd('é')
-- → 'é'

normalize_utf8_nfkc

Folds compatibility characters and recomposes equivalent text into NFKC form.

normalize_utf8_nfkc(string) -> string

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

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

normalize_utf8_nfkd

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

normalize_utf8_nfkd(string) -> string

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

normalize_utf8_nfkd('fi')
-- → 'fi'

not_empty

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

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

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

not_empty('sensor')
-- → true

octet_length

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

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

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

octet_length('é')
-- → 2

overlay

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

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

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

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

overlay_utf8

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

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

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

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

position

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

position(string,string) -> uint64

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

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

position_case_insensitive

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

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

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

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

position_case_insensitive_utf8

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

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

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

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.

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

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

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

regexp_extract

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

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

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

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

regexp_instr

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

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

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

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

regexp_quote_meta

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

regexp_quote_meta(string) -> string

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

regexp_quote_meta('sensor')
-- → 'sensor'

regexp_replace

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

replace_regexp_all(string,string,string) -> string

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

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

regexp_split_to_table

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

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))

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

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.

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

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

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

repeat

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

repeat(string,uint64) -> string

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

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

replace

Substitutes replacement text for every nonoverlapping literal substring occurrence.

replace(string,string,string) -> string

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

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

replace_one

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

replace_one(string,string,string) -> string

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

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

replace_regexp_one

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

replace_regexp_one(string,string,string) -> string

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

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

reverse

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

reverse(string) -> string

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

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

reverse_utf8

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

reverse_utf8(string) -> string

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

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

right

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

right(string,int64) -> string

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

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

right_pad

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

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

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

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

right_pad_utf8

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

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

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

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

right_utf8

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

right_utf8(string,int64) -> string

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

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

split

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

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

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

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

split_by_alpha

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

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

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

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

split_by_char

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

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

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

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

split_by_non_alpha

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

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

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

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

split_by_regexp

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

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

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

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

split_by_whitespace

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

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

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

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

split_part

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

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

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

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

starts_with

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

starts_with(string,string) -> bool

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

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

substring

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

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

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

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

substring_index

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

substring_index(string,string,int64) -> string

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

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

substring_index_utf8

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

substring_index_utf8(string,string,int64) -> string

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

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.

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

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

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

to_valid_utf8

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

to_valid_utf8(x:string|bytes) -> string

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

to_valid_utf8('sensor')
-- → 'sensor'

translate

Substitutes source bytes by positional correspondence between two character lists.

translate(string,string,string) -> string

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

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

translate_utf8

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

translate_utf8(string,string,string) -> string

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

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

trim

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

trim(string) -> string
trim(string,string) -> string

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

trim('  sensor  ')
-- → 'sensor'

trim_left

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

trim_left(string) -> string
trim_left(string,string) -> string

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

trim_left('  sensor  ')
-- → 'sensor  '

trim_right

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

trim_right(string) -> string
trim_right(string,string) -> string

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

trim_right('  sensor  ')
-- → '  sensor'

trim_scale

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

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

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

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

upper

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

upper(string) -> string

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

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

upper_utf8

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

upper_utf8(string) -> string

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

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