Skip to content

functions_string.yaml

This document file is generated for functions_string.yaml

Scalar Functions

concat

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

Concatenate strings.

like

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

  • input: The input string.
  • match: The string to match against the input string.
  • 0. like(opt_enum:case_sensitivity, varchar<L1>, varchar<L2>): -> BOOLEAN
    1. like(opt_enum:case_sensitivity, string, string): -> BOOLEAN

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

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • substring

    Implementations:
    substring(input, start, length): -> return_type
    0. substring(varchar<L1>, i32, i32): -> varchar<L1>
    1. substring(string, i32, i32): -> string
    2. substring(fixedchar<l1>, i32, i32): -> 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.

    regexp_match_substring

    Implementations:
    regexp_match_substring(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, input, pattern, position, occurrence): -> return_type
    0. regexp_match_substring(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, varchar<L1>, varchar<L2>, i64, i64): -> varchar<L1>
    1. regexp_match_substring(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, string, string, i64, i64): -> 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 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.

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • multiline ['MULTILINE_DISABLED', 'MULTILINE_ENABLED']
  • dotall ['DOTALL_DISABLED', 'DOTALL_ENABLED']
  • starts_with

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

  • input: The input string.
  • substring: The substring to search for.
  • 0. starts_with(opt_enum:case_sensitivity, varchar<L1>, varchar<L2>): -> BOOLEAN
    1. starts_with(opt_enum:case_sensitivity, varchar<L1>, string): -> BOOLEAN
    2. starts_with(opt_enum:case_sensitivity, varchar<L1>, fixedchar<L2>): -> BOOLEAN
    3. starts_with(opt_enum:case_sensitivity, string, string): -> BOOLEAN
    4. starts_with(opt_enum:case_sensitivity, string, varchar<L1>): -> BOOLEAN
    5. starts_with(opt_enum:case_sensitivity, string, fixedchar<L1>): -> BOOLEAN
    6. starts_with(opt_enum:case_sensitivity, fixedchar<L1>, fixedchar<L2>): -> BOOLEAN
    7. starts_with(opt_enum:case_sensitivity, fixedchar<L1>, string): -> BOOLEAN
    8. starts_with(opt_enum:case_sensitivity, fixedchar<L1>, varchar<L2>): -> BOOLEAN

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

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • ends_with

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

  • input: The input string.
  • substring: The substring to search for.
  • 0. ends_with(opt_enum:case_sensitivity, varchar<L1>, varchar<L2>): -> BOOLEAN
    1. ends_with(opt_enum:case_sensitivity, varchar<L1>, string): -> BOOLEAN
    2. ends_with(opt_enum:case_sensitivity, varchar<L1>, fixedchar<L2>): -> BOOLEAN
    3. ends_with(opt_enum:case_sensitivity, string, string): -> BOOLEAN
    4. ends_with(opt_enum:case_sensitivity, string, varchar<L1>): -> BOOLEAN
    5. ends_with(opt_enum:case_sensitivity, string, fixedchar<L1>): -> BOOLEAN
    6. ends_with(opt_enum:case_sensitivity, fixedchar<L1>, fixedchar<L2>): -> BOOLEAN
    7. ends_with(opt_enum:case_sensitivity, fixedchar<L1>, string): -> BOOLEAN
    8. ends_with(opt_enum:case_sensitivity, fixedchar<L1>, varchar<L2>): -> BOOLEAN

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

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • contains

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

  • input: The input string.
  • substring: The substring to search for.
  • 0. contains(opt_enum:case_sensitivity, varchar<L1>, varchar<L2>): -> BOOLEAN
    1. contains(opt_enum:case_sensitivity, varchar<L1>, string): -> BOOLEAN
    2. contains(opt_enum:case_sensitivity, varchar<L1>, fixedchar<L2>): -> BOOLEAN
    3. contains(opt_enum:case_sensitivity, string, string): -> BOOLEAN
    4. contains(opt_enum:case_sensitivity, string, varchar<L1>): -> BOOLEAN
    5. contains(opt_enum:case_sensitivity, string, fixedchar<L1>): -> BOOLEAN
    6. contains(opt_enum:case_sensitivity, fixedchar<L1>, fixedchar<L2>): -> BOOLEAN
    7. contains(opt_enum:case_sensitivity, fixedchar<L1>, string): -> BOOLEAN
    8. contains(opt_enum:case_sensitivity, fixedchar<L1>, varchar<L2>): -> BOOLEAN

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

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • strpos

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

  • input: The input string.
  • substring: The substring to search for.
  • 0. strpos(opt_enum:case_sensitivity, string, string): -> i64
    1. strpos(opt_enum:case_sensitivity, varchar<L1>, varchar<L1>): -> i64
    2. strpos(opt_enum:case_sensitivity, fixedchar<L1>, fixedchar<L2>): -> 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.

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • regexp_strpos

    Implementations:
    regexp_strpos(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, input, pattern, position, occurrence): -> return_type
    0. regexp_strpos(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, varchar<L1>, varchar<L2>, i64, i64): -> i64
    1. regexp_strpos(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, string, string, i64, i64): -> 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.

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • multiline ['MULTILINE_DISABLED', 'MULTILINE_ENABLED']
  • dotall ['DOTALL_DISABLED', 'DOTALL_ENABLED']
  • count_substring

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

  • input: The input string.
  • substring: The substring to count.
  • 0. count_substring(opt_enum:case_sensitivity, string, string): -> i64
    1. count_substring(opt_enum:case_sensitivity, varchar<L1>, varchar<L2>): -> i64
    2. count_substring(opt_enum:case_sensitivity, fixedchar<L1>, fixedchar<L2>): -> i64

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

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • regexp_count_substring

    Implementations:
    regexp_count_substring(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, input, pattern, position): -> return_type
    0. regexp_count_substring(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, string, string, i64): -> i64
    1. regexp_count_substring(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, varchar<L1>, varchar<L2>, i64): -> i64
    2. regexp_count_substring(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, fixedchar<L1>, fixedchar<L2>, i64): -> 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.

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • multiline ['MULTILINE_DISABLED', 'MULTILINE_ENABLED']
  • dotall ['DOTALL_DISABLED', 'DOTALL_ENABLED']
  • replace

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

  • input: Input string.
  • substring: The substring to replace.
  • replacement: The replacement string.
  • 0. replace(opt_enum:case_sensitivity, string, string, string): -> string
    1. replace(opt_enum:case_sensitivity, varchar<L1>, varchar<L2>, varchar<L3>): -> varchar<L1>

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

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • concat_ws

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

  • separator: Character to separate strings by.
  • string_arguments: Strings to be concatenated.
  • 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

  • input: Input string.
  • start: The position in the string to start deleting/inserting characters.
  • length: The number of characters to delete from the input string.
  • replacement: The new string to insert at the start position.
  • 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(opt_enum:char_set, input): -> return_type
    0. lower(opt_enum:char_set, string): -> string
    1. lower(opt_enum:char_set, varchar<L1>): -> varchar<L1>
    2. lower(opt_enum:char_set, fixedchar<L1>): -> 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/.

    Options:
  • char_set ['UTF8', 'ASCII_ONLY']
  • upper

    Implementations:
    upper(opt_enum:char_set, input): -> return_type
    0. upper(opt_enum:char_set, string): -> string
    1. upper(opt_enum:char_set, varchar<L1>): -> varchar<L1>
    2. upper(opt_enum:char_set, fixedchar<L1>): -> 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/.

    Options:
  • char_set ['UTF8', 'ASCII_ONLY']
  • swapcase

    Implementations:
    swapcase(opt_enum:char_set, input): -> return_type
    0. swapcase(opt_enum:char_set, string): -> string
    1. swapcase(opt_enum:char_set, varchar<L1>): -> varchar<L1>
    2. swapcase(opt_enum:char_set, fixedchar<L1>): -> 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/.

    Options:
  • char_set ['UTF8', 'ASCII_ONLY']
  • capitalize

    Implementations:
    capitalize(opt_enum:char_set, input): -> return_type
    0. capitalize(opt_enum:char_set, string): -> string
    1. capitalize(opt_enum:char_set, varchar<L1>): -> varchar<L1>
    2. capitalize(opt_enum:char_set, fixedchar<L1>): -> 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/.

    Options:
  • char_set ['UTF8', 'ASCII_ONLY']
  • title

    Implementations:
    title(opt_enum:char_set, input): -> return_type
    0. title(opt_enum:char_set, string): -> string
    1. title(opt_enum:char_set, varchar<L1>): -> varchar<L1>
    2. title(opt_enum:char_set, fixedchar<L1>): -> 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/.

    Options:
  • char_set ['UTF8', 'ASCII_ONLY']
  • 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(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, input, pattern, replacement, position, occurrence): -> return_type

  • input: The input string.
  • pattern: The regular expression to search for within the input string.
  • replacement: The replacement string.
  • position: The position to start the search.
  • occurrence: Which occurrence of the match to replace.
  • 0. regexp_replace(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, string, string, string, i64, i64): -> string
    1. regexp_replace(opt_enum:case_sensitivity, opt_enum:multiline, opt_enum:dotall, varchar<L1>, varchar<L2>, varchar<L3>, i64, i64): -> 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.

    Options:
  • case_sensitivity ['CASE_SENSITIVE', 'CASE_INSENSITIVE', 'CASE_INSENSITIVE_ASCII']
  • multiline ['MULTILINE_DISABLED', 'MULTILINE_ENABLED']
  • dotall ['DOTALL_DISABLED', 'DOTALL_ENABLED']
  • ltrim

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

  • input: The string to remove characters from.
  • characters: The set of characters to remove.
  • 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

  • input: The string to remove characters from.
  • characters: The set of characters to remove.
  • 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

  • input: The string to remove characters from.
  • characters: The set of characters to remove.
  • 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

  • input: The string to pad.
  • length: The length of the output string.
  • characters: The string of characters to use for padding.
  • 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

  • input: The string to pad.
  • length: The length of the output string.
  • characters: The string of characters to use for padding.
  • 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(opt_enum:padding, input, length, character): -> return_type

  • input: The string to pad.
  • length: The length of the output string.
  • character: The character to use for padding.
  • 0. center(opt_enum:padding, varchar<L1>, i32, varchar<1>): -> varchar<L1>
    1. center(opt_enum:padding, string, i32, string): -> 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.

    Options:
  • padding ['RIGHT', 'LEFT']
  • 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.

    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.

    Aggregate Functions

    string_agg

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

  • input: Column of string values.
  • separator: Separator for concatenated strings
  • 0. string_agg(string, string): -> string

    Concatenates a column of string values with a separator.