String functions
append_trailing_char_if_absent
If the str string is not empty and does not contain trailingchar character in the end, it appends trailingchar character to the end. trailing_char can only contain one character. If it contains multiple characters, this function will return NULL.
ascii
This function returns the ascii value of the leftmost character of a given string.
char
CHAR() returns the character value of the given integer value according to the ASCII table.
char_length
This function returns the length of a string. For multi-byte characters, it returns the number of characters. It currently only supports utf8 coding. Note: This function is also named as character_length.
character_length
This function is an alias of charlength, please check charlength function for detailed explanation.
concat
This function combines multiple strings. If any of the parameter value is NULL, it will return NULL.
concat_ws
This function uses the first argument sep as the separator which combines the second with the rest to form a string. If the separator is NULL, the result is NULL. concat_ws does not skip empty strings, but it will skip NULL values.
crc32
Returns the 32-bit cyclic redundancy check (CRC) value of a string. If the input is NULL, NULL is returned.
ends_with
Returns true if a string ends with a specified suffix. Otherwise, it returns false. If the argument is NULL, the result is NULL.
field
Returns the index (position) of a value in a list of values.
find_in_set
This function returns the position of the first str in strlist (start counting with 1). Strlist is a sting separated by commas. If it does not find any str, it returns 0. When the argument is NULL, the result is NULL.
format_bytes
Converts a byte count into a human-readable string with appropriate units (B, KB, MB, GB, TB, PB, EB).
group_concat
Concatenates non-null values from a group into a single string, with a sep argument, which is , by default if not specified. This function can be used to concatenate values from multiple rows of a column into one string.
hex
If x is a numerical value, this function returns a hexadecimal string representation of the value.
hex_decode_binary
Decodes a hex encoded string to a binary.
hex_decode_string
This function performs the opposite operation of hex().
inet_aton
Takes a string containing an IPv4 address in the format A.B.C.D. Returns a BIGINT number representing the corresponding IPv4 address in big endian.
instr
This function returns the position where str first appeared in substr (start counting from 1 and measured in characters). If str is not found in substr, then this function will return 0.
lcase
This function converts a string to lower-case. It is analogous to the function lower.
left
This function returns a specified number of characters from the left side of a given string. The unit for length: utf8 character.
length
This function returns the length of a string (in bytes).
locate
This function is used for finding the location of a substring in a string (starting counting from 1 and measured in characters). If the third argument pos is specified, it will start to find positions of substr in strings below pos. If str is not found, it will return 0.
lower
Converts all strings in an argument to lower-case.
lpad
This function returns strings with a length of len (starting counting from the first syllable) in str. If len is longer than str, the return value is lengthened to len characters by adding pad characters in front of str. If str is longer than len, the return value is shortened to len characters. len means the length of characters, not bytes.
ltrim
Removes the leading spaces or specified characters from the beginning (left) of the str argument. Removing specified characters are supported from StarRocks 2.5.0.
money_format
This function returns a string formatted as a currency string. The integer part is separated by comma every three bits and the decimal part is reserved for two bits.
ngram_search
Calculate the ngram similarity of the two strings.
null_or_empty
This function returns true when the string is empty or NULL. Otherwise, it returns false.
parse_url
Parses a URL and extracts a component from this URL.
regexp_split
Split string str by regexp expression pattern, return maximum max_split elements in ARRAY type.
repeat
This function repeats str by a number of times according to count. When count is below 1, it returns an empty string. When str or count is NULL, it returns NULL.
replace
Replaces all occurrences of characters in a string with another string. This function performs a case-sensitive match when searching for pattern.
reverse
Reverses a string or array. Returns a string or array with the characters in the string or array elements in reverse order.
right
This function returns a specified length of characters from the right side of a given string. Length unit: utf8 character.
rpad
This function returns strings with a length of len (starting counting from the first syllable) in str. If len is longer than str, the return value is lengthened to len characters by adding pad characters behind str. If str is longer than len, the return value is shortened to len characters. len means the length of characters, not bytes.
rtrim
Removes the trailing spaces or specified characters from the end (right) of the str argument. Removing specified characters are supported from StarRocks 2.5.0.
space
Returns a string of the specified number of spaces.
split
This function splits a given string according to the separators, and returns the split parts in ARRAY.
split_part
This function splits a given string according to the separators and returns the requested part. (start counting from the beginning)
starts_with
This function returns 1 when a string starts with a specified prefix. Otherwise, it returns 0. When the argument is NULL, the result is NULL.
str_to_map
Splits a given string into key-value pairs using two delimiters and returns a map of the split pairs.
strcmp
This function compares two strings. Returns 0 if lhs and rhs compare equal. Return -1 if lhs appears before rhs in lexicographical order. Returns 1 if lhs appears after rhs in lexicographical order. When the argument is NULL, the result is NULL.
strleft
This function extracts a number of characters from a string with specified length (starting from left). The unit for length: utf8 character.
strpos
Returns the position of the N-th substring in a string. If N is a negative number, the search will start from the end of the string. Positions start with 1. If not found, 0 is returned.
strright
This function extracts a number of characters from a string with specified length (starting from right). The unit for length: utf-8 character.
substring, substr
Extracts characters staring from the specified position and returns a substring of specified length.
substring_index
Extracts a substring that precedes or follows the count occurrences of the delimiter.
tokenize
Splits and parses text into tokens based on the specified tokenizer.
translate
Substitutes specified characters within a string. It works by taking a string (source) as the input and replaces the fromstring characters in source with tostring.
trim
Removes consecutive spaces or specified characters from the beginning and end of the str argument. Removing specified characters are supported from StarRocks 2.5.0.
ucase
This function converts a string to upper-case. It is analogous to the function upper.
unhex
This function performs the opposite operation of hex().
upper
Converts a string to upper-case.
url_decode
Translates a string back from the application/x-www-form-urlencoded format. This function is an inverse of urlencode.
url_encode
Translates a string into the application/x-www-form-urlencoded format.
url_extract_host
Extracts the host section from a URL.
url_extract_parameter
Extracts the value of the requested name parameter from the query string of a URL. Parameter extraction is handled in the manner as specified in RFC 1866#section-8.2.1. If the specified parameter name does not exist, NULL is returned.