String Functions

Function List

lpad

This function pads the beginning of a string with specified characters until it reaches the specified length, and then returns the result string. If you do not specify padding characters, space characters are used for padding.

Syntax: lpad(str, len, lpadStr)

Return value type: string

Example: SELECT LPAD('hello world', 10), LPAD('hello', 10, 'e')

rpad

This function pads the end of a string with specified characters until it reaches the specified length, and then returns the result string. If you do not specify padding characters, space characters are used for padding.

Syntax: rpad(str, len, rpadStr)

Return value type: string

Example: SELECT RPAD('hello world', 10), RPAD('hello', 10, 'e')

replace

This function replaces all occurrences of a matched substring in the original string with a specified string. If no replacement string is specified, all matched substrings are removed from the original string.

Syntax: replace(str, subStr, replaceStr)

Return value type: string

Example: SELECT REPLACE('hello world', 'o'), REPLACE('hello world', 'w', 'new w')

reverse

This function reverses the order of characters in a string.

Syntax: reverse(str)

Return value type: string

Example: SELECT REVERSE('hello world')

repeat

This function repeats a string a specified number of times.

Syntax: repeat(str, num)

Return value type: string

Example: SELECT REPEAT('hello world', 2), REPEAT('hello world', 0)

contains_string

This function checks whether a string contains a specified substring.

Syntax: contains_string(str, containsStr)

Return value type: Boolean

Example: SELECT CONTAINS_STRING('hello world', 'llo')

icontains_string

This function checks whether a string contains a specified substring, regardless of letter case.

Syntax: icontains_string(str, containsStr)

Return value type: Boolean

Example: SELECT ICONTAINS_STRING('hello world', 'llo')

textcat

This function concatenates two strings and returns the concatenated result.

Syntax: textcat(str1, str2)

Return value type: string

Example: SELECT TEXTCAT('hello ', 'world')

btrim

This function removes spaces from both sides of a string.

Syntax: btrim(str)

Return value type: string

Example: SELECT BTRIM(' hello world ')

parse_long

This function converts a string to a long integer using a specified radix (base). If no radix is provided, the default radix of 10 (decimal) is used.

Syntax: parse_long(str, radix)

Return value type: long

Example: SELECT PARSE_LONG('-1234', 8), PARSE_LONG('1234')

split

This function splits a string using a specified delimiter and returns an array of substrings. You can set limit to limit the number of resulting substrings.

Syntax: split(str, splitStr, limit)

Return value type: ARRAYS<STRING>

Example: SELECT SPLIT('helloworld', 'o'), SPLIT('helloworld', 'o', 2)

split_part

This function splits a string using a specified delimiter and returns the substring at a specified position. If the position exceeds the array length, an empty string is returned.

Syntax: split_part(str, splitStr, position)

Return value type: string

Example: SELECT SPLIT_PART('helloworld', 'o', 2), SPLIT_PART('helloworld', 'o', 4)

split_to_map

This function splits a string using the first delimiter, then splits the string further using the second delimiter, and returns the result.

Syntax: split_to_map(str, splitStr1, splitStr2)

Return value type: array

Example: SELECT SPLIT_TO_MAP('upstream_response_time:123, request_time:456', ',', ':')

string_format

This function returns a string formatted using Java's String.format method.

Syntax: string_format(str, Object... args)

Return value type: string

Example: SELECT STRING_FORMAT('My name is %s and I am %d years old.', 'name', age)

• %d: outputs an integer.
• %f: outputs a floating point number.
• %s: outputs a string.
• %n: outputs a newline character.
• %c: outputs characters.
• %b: outputs the Boolean type.
• %e: outputs the exponential type.
• %tc: outputs date and time information.
• %tF: The output format is YYYY-MM-DD.
• %tr: The output format is HH:MM:SS PM.

strpos

This function returns the position of the first occurrence of the target substring in a string. It returns 0 if the target substring is not found.

Syntax: strpos(str, subStr)

Return value type: integer

Example: SELECT STRPOS('hello world', 'llo'), STRPOS('llo', 'hello world')

substr

This function extracts a substring based on the start position and length. If the length is not specified, it returns the entire string starting from the start position. It is equivalent to the substring function.

Syntax: substr(str, start, length)

Return value type: string

Example: SELECT SUBSTR('helloworld', 5), SUBSTR('helloworld', 5, 3)

substring

This function extracts a substring based on the start position and length. If the length is not specified, it returns the entire string starting from the start position.

Syntax: substring(str, start, length)

Return value type: string

Example: SELECT SUBSTRING('helloworld', 5), SUBSTRING('helloworld', 5, 3)

length

This function returns the length of the input characters (for a string expression) or the byte length (for a non-string expression). The byte length is at least the smallest integer obtained by dividing the number of digits by 8.

Syntax: length(str)

Return value type: integer

Example: SELECT LENGTH('HELLO WORLD')

char_length

This function returns the length of the input characters (for a string expression) or the byte length (for a non-string expression). The byte length is at least the smallest integer obtained by dividing the number of digits by 8. This function is equivalent to the length function.

Syntax: char_length(str)

Return value type: integer

Example: SELECT CHAR_LENGTH('HELLO WORLD')

character_length

This function is equivalent to the length function.

Syntax: character_length(str)

Return value type: integer

Example: SELECT CHARACTER_LENGTH('HELLO WORLD')

strlen

This function is equivalent to the length function.

Syntax: strlen(str)

Return value type: integer

Example: SELECT STRLEN('HELLO WORLD')

levenshtein_distance

This function calculates the minimum edit distance between strings str1 and str2.

Syntax: levenshtein_distance(str1, str2)

Return value type: integer

Example: SELECT LEVENSHTEIN_DISTANCE('horse', 'ros')

normalize

This function formats a string (str).

• Formats str using the NFC form.

  Syntax: normalize(str)

• Formats str using a specified form. The supported forms include NFC, NFD, NFKC, and NFKD.

  Syntax: normalize(str, form)

Return value type: string

Example: SELECT NORMALIZE('schön'), NORMALIZE('Henry \u2163', 'nfd')

to_utf8

This function converts a string to the UTF-8 encoding format.

Syntax: to_utf8(str)

Return value type: integer

Example: SELECT TO_UTF8('%')

chr

This function converts an ASCII value to characters.

Syntax: chr(ascii)

Return value type: string

Example: SELECT CHR(99)

concat

This function concatenates multiple parameters into a string.

Syntax: concat(x, y...)

Return value type: string

Example: select concat('1','+','1')