functions_string.yaml¶

This document file is generated for functions_string.yaml

Scalar Functions¶

concat¶

Implementations:
concat(input, option:null_handling): -> return_type
0. concat(varchar<L1>, option:null_handling): -> varchar<L1>
1. concat(string, option:null_handling): -> string

Concatenate strings. The null_handling option determines whether or not null values will be recognized by the function. If null_handling is set to IGNORE_NULLS, null value arguments will be ignored when strings are concatenated. If set to ACCEPT_NULLS, the result will be null if any argument passed to the concat function is null.

like¶

Implementations:
like(input, match, option:case_sensitivity): -> return_type

0. like(varchar<L1>, varchar<L2>, option:case_sensitivity): -> boolean
1. like(string, string, option:case_sensitivity): -> boolean

Are two strings like each other. The case_sensitivity option applies to the match argument.

substring¶

Implementations:
substring(input, start, length, option:negative_start): -> return_type
0. substring(varchar<L1>, i32, i32, option:negative_start): -> varchar<L1>
1. substring(string, i32, i32, option:negative_start): -> string
2. substring(fixedchar<l1>, i32, i32, option:negative_start): -> string
3. substring(varchar<L1>, i32, option:negative_start): -> varchar<L1>
4. substring(string, i32, option:negative_start): -> string
5. substring(fixedchar<l1>, i32, option:negative_start): -> string

Extract a substring of a specified length starting from position start. A start value of 1 refers to the first characters of the string. When length is not specified the function will extract a substring starting from position start and ending at the end of the string. The negative_start option applies to the start parameter. WRAP_FROM_END means the index will start from the end of the input and move backwards. The last character has an index of -1, the second to last character has an index of -2, and so on. LEFT_OF_BEGINNING means the returned substring will start from the left of the first character. A start of -1 will begin 2 characters left of the the input, while a start of 0 begins 1 character left of the input.

regexp_match_substring¶

Implementations:
regexp_match_substring(input, pattern, position, occurrence, group, option:case_sensitivity, option:multiline, option:dotall): -> return_type
0. regexp_match_substring(varchar<L1>, varchar<L2>, i64, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> varchar<L1>
1. regexp_match_substring(string, string, i64, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> string

Extract a substring that matches the given regular expression pattern. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The occurrence of the pattern to be extracted is specified using the occurrence argument. Specifying 1 means the first occurrence will be extracted, 2 means the second occurrence, and so on. The occurrence argument should be a positive non-zero integer. The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the position argument. Specifying 1 means to search for matches starting at the first character of the input string, 2 means the second character, and so on. The position argument should be a positive non-zero integer. The regular expression capture group can be specified using the group argument. Specifying 0 will return the substring matching the full regular expression. Specifying 1 will return the substring matching only the first capture group, and so on. The group argument should be a non-negative integer. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile, the occurrence value is out of range, the position value is out of range, or the group value is out of range.

regexp_match_substring¶

Implementations:
regexp_match_substring(input, pattern, option:case_sensitivity, option:multiline, option:dotall): -> return_type
0. regexp_match_substring(string, string, option:case_sensitivity, option:multiline, option:dotall): -> string

Extract a substring that matches the given regular expression pattern. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The first occurrence of the pattern from the beginning of the string is extracted. It returns the substring matching the full regular expression. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile.

regexp_match_substring_all¶

Implementations:
regexp_match_substring_all(input, pattern, position, group, option:case_sensitivity, option:multiline, option:dotall): -> return_type
0. regexp_match_substring_all(varchar<L1>, varchar<L2>, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> List<varchar<L1>>
1. regexp_match_substring_all(string, string, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> List<string>

Extract all substrings that match the given regular expression pattern. This will return a list of extracted strings with one value for each occurrence of a match. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the position argument. Specifying 1 means to search for matches starting at the first character of the input string, 2 means the second character, and so on. The position argument should be a positive non-zero integer. The regular expression capture group can be specified using the group argument. Specifying 0 will return substrings matching the full regular expression. Specifying 1 will return substrings matching only the first capture group, and so on. The group argument should be a non-negative integer. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile, the position value is out of range, or the group value is out of range.

starts_with¶

Implementations:
starts_with(input, substring, option:case_sensitivity): -> return_type

0. starts_with(varchar<L1>, varchar<L2>, option:case_sensitivity): -> boolean
1. starts_with(varchar<L1>, string, option:case_sensitivity): -> boolean
2. starts_with(varchar<L1>, fixedchar<L2>, option:case_sensitivity): -> boolean
3. starts_with(string, string, option:case_sensitivity): -> boolean
4. starts_with(string, varchar<L1>, option:case_sensitivity): -> boolean
5. starts_with(string, fixedchar<L1>, option:case_sensitivity): -> boolean
6. starts_with(fixedchar<L1>, fixedchar<L2>, option:case_sensitivity): -> boolean
7. starts_with(fixedchar<L1>, string, option:case_sensitivity): -> boolean
8. starts_with(fixedchar<L1>, varchar<L2>, option:case_sensitivity): -> boolean

Whether the input string starts with the substring. The case_sensitivity option applies to the substring argument.

ends_with¶

Implementations:
ends_with(input, substring, option:case_sensitivity): -> return_type

0. ends_with(varchar<L1>, varchar<L2>, option:case_sensitivity): -> boolean
1. ends_with(varchar<L1>, string, option:case_sensitivity): -> boolean
2. ends_with(varchar<L1>, fixedchar<L2>, option:case_sensitivity): -> boolean
3. ends_with(string, string, option:case_sensitivity): -> boolean
4. ends_with(string, varchar<L1>, option:case_sensitivity): -> boolean
5. ends_with(string, fixedchar<L1>, option:case_sensitivity): -> boolean
6. ends_with(fixedchar<L1>, fixedchar<L2>, option:case_sensitivity): -> boolean
7. ends_with(fixedchar<L1>, string, option:case_sensitivity): -> boolean
8. ends_with(fixedchar<L1>, varchar<L2>, option:case_sensitivity): -> boolean

Whether input string ends with the substring. The case_sensitivity option applies to the substring argument.

contains¶

Implementations:
contains(input, substring, option:case_sensitivity): -> return_type

0. contains(varchar<L1>, varchar<L2>, option:case_sensitivity): -> boolean
1. contains(varchar<L1>, string, option:case_sensitivity): -> boolean
2. contains(varchar<L1>, fixedchar<L2>, option:case_sensitivity): -> boolean
3. contains(string, string, option:case_sensitivity): -> boolean
4. contains(string, varchar<L1>, option:case_sensitivity): -> boolean
5. contains(string, fixedchar<L1>, option:case_sensitivity): -> boolean
6. contains(fixedchar<L1>, fixedchar<L2>, option:case_sensitivity): -> boolean
7. contains(fixedchar<L1>, string, option:case_sensitivity): -> boolean
8. contains(fixedchar<L1>, varchar<L2>, option:case_sensitivity): -> boolean

Whether the input string contains the substring. The case_sensitivity option applies to the substring argument.

strpos¶

Implementations:
strpos(input, substring, option:case_sensitivity): -> return_type

0. strpos(string, string, option:case_sensitivity): -> i64
1. strpos(varchar<L1>, varchar<L1>, option:case_sensitivity): -> i64
2. strpos(fixedchar<L1>, fixedchar<L2>, option:case_sensitivity): -> i64

Return the position of the first occurrence of a string in another string. The first character of the string is at position 1. If no occurrence is found, 0 is returned. The case_sensitivity option applies to the substring argument.

regexp_strpos¶

Implementations:
regexp_strpos(input, pattern, position, occurrence, option:case_sensitivity, option:multiline, option:dotall): -> return_type
0. regexp_strpos(varchar<L1>, varchar<L2>, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> i64
1. regexp_strpos(string, string, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> i64

Return the position of an occurrence of the given regular expression pattern in a string. The first character of the string is at position 1. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the position argument. Specifying 1 means to search for matches starting at the first character of the input string, 2 means the second character, and so on. The position argument should be a positive non-zero integer. Which occurrence to return the position of is specified using the occurrence argument. Specifying 1 means the position first occurrence will be returned, 2 means the position of the second occurrence, and so on. The occurrence argument should be a positive non-zero integer. If no occurrence is found, 0 is returned. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile, the occurrence value is out of range, or the position value is out of range.

count_substring¶

Implementations:
count_substring(input, substring, option:case_sensitivity): -> return_type

0. count_substring(string, string, option:case_sensitivity): -> i64
1. count_substring(varchar<L1>, varchar<L2>, option:case_sensitivity): -> i64
2. count_substring(fixedchar<L1>, fixedchar<L2>, option:case_sensitivity): -> i64

Return the number of non-overlapping occurrences of a substring in an input string. The case_sensitivity option applies to the substring argument.

regexp_count_substring¶

Implementations:
regexp_count_substring(input, pattern, position, option:case_sensitivity, option:multiline, option:dotall): -> return_type
0. regexp_count_substring(string, string, i64, option:case_sensitivity, option:multiline, option:dotall): -> i64
1. regexp_count_substring(varchar<L1>, varchar<L2>, i64, option:case_sensitivity, option:multiline, option:dotall): -> i64
2. regexp_count_substring(fixedchar<L1>, fixedchar<L2>, i64, option:case_sensitivity, option:multiline, option:dotall): -> i64

Return the number of non-overlapping occurrences of a regular expression pattern in an input string. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the position argument. Specifying 1 means to search for matches starting at the first character of the input string, 2 means the second character, and so on. The position argument should be a positive non-zero integer. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile or the position value is out of range.

regexp_count_substring¶

Implementations:
regexp_count_substring(input, pattern, option:case_sensitivity, option:multiline, option:dotall): -> return_type
0. regexp_count_substring(string, string, option:case_sensitivity, option:multiline, option:dotall): -> i64

Return the number of non-overlapping occurrences of a regular expression pattern in an input string. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The match starts at the first character of the input string. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile.

replace¶

Implementations:
replace(input, substring, replacement, option:case_sensitivity): -> return_type

0. replace(string, string, string, option:case_sensitivity): -> string
1. replace(varchar<L1>, varchar<L2>, varchar<L3>, option:case_sensitivity): -> varchar<L1>

Replace all occurrences of the substring with the replacement string. The case_sensitivity option applies to the substring argument.

concat_ws¶

Implementations:
concat_ws(separator, string_arguments): -> return_type

0. concat_ws(string, string): -> string
1. concat_ws(varchar<L2>, varchar<L1>): -> varchar<L1>

Concatenate strings together separated by a separator.

repeat¶

Implementations:
repeat(input, count): -> return_type
0. repeat(string, i64): -> string
1. repeat(varchar<L1>, i64, i64): -> varchar<L1>

Repeat a string count number of times.

reverse¶

Implementations:
reverse(input): -> return_type
0. reverse(string): -> string
1. reverse(varchar<L1>): -> varchar<L1>
2. reverse(fixedchar<L1>): -> fixedchar<L1>

Returns the string in reverse order.

replace_slice¶

Implementations:
replace_slice(input, start, length, replacement): -> return_type

0. replace_slice(string, i64, i64, string): -> string
1. replace_slice(varchar<L1>, i64, i64, varchar<L2>): -> varchar<L1>

Replace a slice of the input string. A specified ‘length’ of characters will be deleted from the input string beginning at the ‘start’ position and will be replaced by a new string. A start value of 1 indicates the first character of the input string. If start is negative or zero, or greater than the length of the input string, a null string is returned. If ‘length’ is negative, a null string is returned. If ‘length’ is zero, inserting of the new string occurs at the specified ‘start’ position and no characters are deleted. If ‘length’ is greater than the input string, deletion will occur up to the last character of the input string.

lower¶

Implementations:
lower(input, option:char_set): -> return_type
0. lower(string, option:char_set): -> string
1. lower(varchar<L1>, option:char_set): -> varchar<L1>
2. lower(fixedchar<L1>, option:char_set): -> fixedchar<L1>

Transform the string to lower case characters. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.

upper¶

Implementations:
upper(input, option:char_set): -> return_type
0. upper(string, option:char_set): -> string
1. upper(varchar<L1>, option:char_set): -> varchar<L1>
2. upper(fixedchar<L1>, option:char_set): -> fixedchar<L1>

Transform the string to upper case characters. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.

swapcase¶

Implementations:
swapcase(input, option:char_set): -> return_type
0. swapcase(string, option:char_set): -> string
1. swapcase(varchar<L1>, option:char_set): -> varchar<L1>
2. swapcase(fixedchar<L1>, option:char_set): -> fixedchar<L1>

Transform the string’s lowercase characters to uppercase and uppercase characters to lowercase. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.

capitalize¶

Implementations:
capitalize(input, option:char_set): -> return_type
0. capitalize(string, option:char_set): -> string
1. capitalize(varchar<L1>, option:char_set): -> varchar<L1>
2. capitalize(fixedchar<L1>, option:char_set): -> fixedchar<L1>

Capitalize the first character of the input string. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.

title¶

Implementations:
title(input, option:char_set): -> return_type
0. title(string, option:char_set): -> string
1. title(varchar<L1>, option:char_set): -> varchar<L1>
2. title(fixedchar<L1>, option:char_set): -> fixedchar<L1>

Converts the input string into titlecase. Capitalize the first character of each word in the input string except for articles (a, an, the). Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.

initcap¶

Implementations:
initcap(input, option:char_set): -> return_type
0. initcap(string, option:char_set): -> string
1. initcap(varchar<L1>, option:char_set): -> varchar<L1>
2. initcap(fixedchar<L1>, option:char_set): -> fixedchar<L1>

Capitalizes the first character of each word in the input string, including articles, and lowercases the rest. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.

char_length¶

Implementations:
char_length(input): -> return_type
0. char_length(string): -> i64
1. char_length(varchar<L1>): -> i64
2. char_length(fixedchar<L1>): -> i64

Return the number of characters in the input string. The length includes trailing spaces.

bit_length¶

Implementations:
bit_length(input): -> return_type
0. bit_length(string): -> i64
1. bit_length(varchar<L1>): -> i64
2. bit_length(fixedchar<L1>): -> i64

Return the number of bits in the input string.

octet_length¶

Implementations:
octet_length(input): -> return_type
0. octet_length(string): -> i64
1. octet_length(varchar<L1>): -> i64
2. octet_length(fixedchar<L1>): -> i64

Return the number of bytes in the input string.

regexp_replace¶

Implementations:
regexp_replace(input, pattern, replacement, position, occurrence, option:case_sensitivity, option:multiline, option:dotall): -> return_type

0. regexp_replace(string, string, string, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> string
1. regexp_replace(varchar<L1>, varchar<L2>, varchar<L3>, i64, i64, option:case_sensitivity, option:multiline, option:dotall): -> varchar<L1>

Search a string for a substring that matches a given regular expression pattern and replace it with a replacement string. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github .io/icu/userguide/strings/regexp.html). The occurrence of the pattern to be replaced is specified using the occurrence argument. Specifying 1 means only the first occurrence will be replaced, 2 means the second occurrence, and so on. Specifying 0 means all occurrences will be replaced. The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the position argument. Specifying 1 means to search for matches starting at the first character of the input string, 2 means the second character, and so on. The position argument should be a positive non-zero integer. The replacement string can capture groups using numbered backreferences. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile, the replacement contains an illegal back-reference, the occurrence value is out of range, or the position value is out of range.

regexp_replace¶

Implementations:
regexp_replace(input, pattern, replacement, option:case_sensitivity, option:multiline, option:dotall): -> return_type

0. regexp_replace(string, string, string, option:case_sensitivity, option:multiline, option:dotall): -> string

Search a string for a substring that matches a given regular expression pattern and replace it with a replacement string. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github .io/icu/userguide/strings/regexp.html). The replacement string can capture groups using numbered backreferences. All occurrences of the pattern will be replaced. The search for matches start at the first character of the input. The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string. Behavior is undefined if the regex fails to compile or the replacement contains an illegal back-reference.

ltrim¶

Implementations:
ltrim(input, characters): -> return_type

0. ltrim(varchar<L1>, varchar<L2>): -> varchar<L1>
1. ltrim(string, string): -> string

Remove any occurrence of the characters from the left side of the string. If no characters are specified, spaces are removed.

rtrim¶

Implementations:
rtrim(input, characters): -> return_type

0. rtrim(varchar<L1>, varchar<L2>): -> varchar<L1>
1. rtrim(string, string): -> string

Remove any occurrence of the characters from the right side of the string. If no characters are specified, spaces are removed.

trim¶

Implementations:
trim(input, characters): -> return_type

0. trim(varchar<L1>, varchar<L2>): -> varchar<L1>
1. trim(string, string): -> string

Remove any occurrence of the characters from the left and right sides of the string. If no characters are specified, spaces are removed.

lpad¶

Implementations:
lpad(input, length, characters): -> return_type

0. lpad(varchar<L1>, i32, varchar<L2>): -> varchar<L1>
1. lpad(string, i32, string): -> string

Left-pad the input string with the string of ‘characters’ until the specified length of the string has been reached. If the input string is longer than ‘length’, remove characters from the right-side to shorten it to ‘length’ characters. If the string of ‘characters’ is longer than the remaining ‘length’ needed to be filled, only pad until ‘length’ has been reached. If ‘characters’ is not specified, the default value is a single space.

rpad¶

Implementations:
rpad(input, length, characters): -> return_type

0. rpad(varchar<L1>, i32, varchar<L2>): -> varchar<L1>
1. rpad(string, i32, string): -> string

Right-pad the input string with the string of ‘characters’ until the specified length of the string has been reached. If the input string is longer than ‘length’, remove characters from the left-side to shorten it to ‘length’ characters. If the string of ‘characters’ is longer than the remaining ‘length’ needed to be filled, only pad until ‘length’ has been reached. If ‘characters’ is not specified, the default value is a single space.

center¶

Implementations:
center(input, length, character, option:padding): -> return_type

0. center(varchar<L1>, i32, varchar<L1>, option:padding): -> varchar<L1>
1. center(string, i32, string, option:padding): -> string

Center the input string by padding the sides with a single character until the specified length of the string has been reached. By default, if the length will be reached with an uneven number of padding, the extra padding will be applied to the right side. The side with extra padding can be controlled with the padding option. Behavior is undefined if the number of characters passed to the character argument is not 1.

left¶

Implementations:
left(input, count): -> return_type
0. left(varchar<L1>, i32): -> varchar<L1>
1. left(string, i32): -> string

Extract count characters starting from the left of the string.

right¶

Implementations:
right(input, count): -> return_type
0. right(varchar<L1>, i32): -> varchar<L1>
1. right(string, i32): -> string

Extract count characters starting from the right of the string.

string_split¶

Implementations:
string_split(input, separator): -> return_type

0. string_split(varchar<L1>, varchar<L2>): -> List<varchar<L1>>
1. string_split(string, string): -> List<string>

Split a string into a list of strings, based on a specified separator character.

regexp_string_split¶

Implementations:
regexp_string_split(input, pattern, option:case_sensitivity, option:multiline, option:dotall): -> return_type

0. regexp_string_split(varchar<L1>, varchar<L2>, option:case_sensitivity, option:multiline, option:dotall): -> List<varchar<L1>>
1. regexp_string_split(string, string, option:case_sensitivity, option:multiline, option:dotall): -> List<string>

Split a string into a list of strings, based on a regular expression pattern. The substrings matched by the pattern will be used as the separators to split the input string and will not be included in the resulting list. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The case_sensitivity option specifies case-sensitive or case-insensitive matching. Enabling the multiline option will treat the input string as multiple lines. This makes the ^ and $ characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the dotall option makes the . character match line terminator characters in a string.

Aggregate Functions¶

string_agg¶

Implementations:
string_agg(input, separator): -> return_type

0. string_agg(string, string): -> string

Concatenates a column of string values with a separator.