Skip to main content
Version: 2.4.x

String Functions

String functions are a collection of functions used to handle and manipulate text strings.They include operations such as string concatenation, string splitting, string searching and replacing, and string length calculation.String functions can help you manipulate and transform text data, perform string matching and processing.

ascii

Returns the ASCII value of the first character in a string.

ascii(str)
ParametersDescription
strString expression to operate on.Can be a constant, column, or function, and any combination of string operators.
View ascii Example
SELECT ascii('abc');
+--------------------+
| ascii(Utf8("abc")) |
+--------------------+
| 97 |
+--------------------+

Related functions: chr

bit_length

Returns the bit length of a string.

bit_length(str)
ParametersDescription
strString expression to operate on.Can be a constant, column, or function, and any combination of string operators.
View bit_length Example
SELECT bit_length('abc');
+-------------------------+
| bit_length(Utf8("abc")) |
+-------------------------+
| 24 |
+-------------------------+

Related functions: length, octet_length

btrim

Trims the specified trim string from the start and end of a string.If no trim string is provided, all whitespace is removed from the start and end of the input string.

btrim(str[, trim_str])
ParametersDescription
strString expression to operate on.Can be a constant, column, or function, and any combination of string operators.
trim_strString expression to trim from the beginning and end of the input string.Can be a constant, column, or function, and any combination of arithmetic operators.Default is whitespace characters.
View btrim Example
SELECT btrim('111abc111','1');
+------------------------------------+
| btrim(Utf8("111abc111"),Utf8("1")) |
+------------------------------------+
| abc |
+------------------------------------+

Related functions: ltrim, rtrim, trim

char_length

Alias of length

character_length

Alias of length

concat

Concatenates multiple strings together.

concat(separator, str[, ..., str_n])
ParametersDescription
separatorSeparator used to insert between concatenated strings.
strString expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
str_nSubsequent string column or literal string to concatenate.
View concat Example
SELECT concat('a', 'b', 'c');
+---------------------------------------+
| concat(Utf8("a"),Utf8("b"),Utf8("c")) |
+---------------------------------------+
| abc |
+---------------------------------------+

Related functions: concat_ws

concat_ws

Concatenates multiple strings together with the specified separator.

concat_ws(separator, str[, ..., str_n])
ParametersDescription
separatorSeparator used to insert between concatenated strings.
strString expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
str_nSubsequent string column or literal string to concatenate.
View concat_ws Example
SELECT concat_ws(' ', 'a', 'b', 'c');
+----------------------------------------------------+
| concat_ws(Utf8(" "),Utf8("a"),Utf8("b"),Utf8("c")) |
+----------------------------------------------------+
| a b c |
+----------------------------------------------------+

Related functions: concat

chr

Returns the character with the specified ASCII or Unicode code value.

chr(expression)
ParametersDescription
expressionExpression containing the ASCII or Unicode code values to operate on.Can be a constant, column, or function, and any combination of arithmetic or string operators.
View chr Example
SELECT chr(20005);
+-------------------+
| chr(Int64(20005)) |
+-------------------+
||
+-------------------+

Related functions: ascii

initcap

Capitalize the first character of each word in the input string.Words are separated by non-alphanumeric characters.

initcap(str)
ParametersDescription
strCan be a constant, column, or function, and any combination of string operators.
View initcap Example
SELECT initcap('hello world');


+------------------------------+
| initcap(Utf8("hello world")) |
+------------------------------+
| Hello World |
+------------------------------+

Related functions:lowerupper

instr

Alias of strpos.

left

Returns a specified number of characters starting from the left side of the string.

left(str, n)
ParametersDescription
strString expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
nNumber of characters to return.
View left Example
SELECT left('abcde', 3);
+------------------------------+
| left(Utf8("abcde"),Int64(3)) |
+------------------------------+
| abc |
+------------------------------+

length

Returns the number of characters in a string.

length(str)
View length Example
SELECT length('Hello CnosDB!');
+-----------------------------------------+
| character_length(Utf8("Hello CnosDB!")) |
+-----------------------------------------+
| 13 |
+-----------------------------------------+

Related functions: bit_length,octet_length

lower

Convert string to lower-case.

lower(str)
View lower Example
SELECT lower('CNOSDB');
+-----------------------+
| lower(Utf8("CNOSDB")) |
+-----------------------+
| cnosdb |
+-----------------------+

Related functions:initcap,upper

lpad

Pads the left side of a string with another string to a specified string length.

lpad(str, n[, padding_str])
ParametersDescription
strString expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
nString length to pad to.
padding_strString expression to pad with.Can be a constant, column, or function, and any combination of string operators.Default is a space.
View lpad Example
SELECT lpad('abc', 10, '1');
+---------------------------------------+
| lpad(Utf8("abc"),Int64(10),Utf8("1")) |
+---------------------------------------+
| 1111111abc |
+---------------------------------------+

Related functions:rpad

ltrim

Remove the spaces on the left side of the string.

ltrim(str[, trim_str])
ParametersDescription
strString expression to operate on.Can be a constant, column, or function, and any combination of string operators.
trim_strString expression to trim from the beginning of the input string.Can be a constant, column, or function, and any combination of arithmetic operators.Default is whitespace characters.
View ltrim Example
SELECT ltrim('   abc');
+-----------------------+
| ltrim(Utf8(" abc")) |
+-----------------------+
| abc |
+-----------------------+

Related functions: btrim,rtrim,trim

octet_length

Returns the length of a string (in bytes).

octet_length(str)
View octet_length Example
SELECT octet_length('Hello');
+-----------------------------+
| octet_length(Utf8("Hello")) |
+-----------------------------+
| 5 |
+-----------------------------+

Related functions: bit_length,length

repeat

Returns a string with an input string repeated a specified number.

repeat(str, n)
ParametersDescription
strString expression to repeat.Can be a constant, column, or function, and any combination of string operators.
nNumber of times to repeat the input string.
View repeat Example
SELECT repeat('a', 5);
+----------------------------+
| repeat(Utf8("a"),Int64(5)) |
+----------------------------+
| aaaaa |
+----------------------------+

replace

Replaces all occurrences of a specified substring in a string with a new substring.

replace(str, substr, replacement)
ParametersDescription
strString expression to repeat.Can be a constant, column, or function, and any combination of string operators.
substrSubstring expression to replace in the input string.Can be a constant, column, or function, and any combination of string operators.
replacementReplacement substring expression.Can be a constant, column, or function, and any combination of string operators.
View replace Example
SELECT replace('aaa', 'a', 'b');
+------------------------------------------+
| replace(Utf8("aaa"),Utf8("a"),Utf8("b")) |
+------------------------------------------+
| bbb |
+------------------------------------------+

reverse

Reverse the order of characters in the string.

reverse(str)
View reverse Example
SELECT reverse('hello');
+------------------------+
| reverse(Utf8("hello")) |
+------------------------+
| olleh |
+------------------------+

Returns a specified number of characters from the right side of a string.

right(str, n)
ParametersDescription
strString expression to repeat.Can be a constant, column, or function, and any combination of string operators.
nNumber of characters to return.
View repeat Example
SELECT right('aaabbb', 3);
+--------------------------------+
| right(Utf8("aaabbb"),Int64(3)) |
+--------------------------------+
| bbb |
+--------------------------------+

Related functions:left

rpad

Pads the right side of a string with another string to a specified string length.

rpad(str, n[, padding_str])
ParametersDescription
strString expression to repeat.Can be a constant, column, or function, and any combination of string operators.
nString length to pad to.
padding_strString expression to pad with.Can be a constant, column, or function, and any combination of string operators.Default is a space.
View rpad Example
SELECT rpad('aaa', 10, 'b');
+---------------------------------------+
| rpad(Utf8("aaa"),Int64(10),Utf8("b")) |
+---------------------------------------+
| aaabbbbbbb |
+---------------------------------------+

Related functions:lpad

rtrim

Remove trailing spaces from a string.

rtrim(str)
View rtrim Example
SELECT rtrim('aaabbb', 'b');
+---------------------------------+
| rtrim(Utf8("aaabbb"),Utf8("b")) |
+---------------------------------+
| aaa |
+---------------------------------+

Related functions: btrim, ltrim, trim

split_part

Splits a string based on a specified delimiter and returns the substring in the specified position.

split_part(str, delimiter, pos)
ParametersDescription
strString expression to repeat.Can be a constant, column, or function, and any combination of string operators.
delimiterString or character to split on.
posPosition of the part to return.
View split_part Example
SELECT split_part('abc|def|ghi', '|', 2);
+----------------------------------------------------+
| split_part(Utf8("abc|def|ghi"),Utf8("|"),Int64(2)) |
+----------------------------------------------------+
| def |
+----------------------------------------------------+

starts_with

Tests if a string starts with a substring.

starts_with(str, substr)
View split_part Example
SELECT starts_with('abcdefg', 'abc');
+------------------------------------------+
| starts_with(Utf8("abcdefg"),Utf8("abc")) |
+------------------------------------------+
| true |
+------------------------------------------+

strpos

Returns the starting position of a specified substring in a string.Positions begin at 1.If the substring does not exist in the string, the function returns 0.

strpos(str, substr)

Equivalent to instr

View strpos Example
SELECT strpos('abcdef', 'def');
+------------------------------------+
| strpos(Utf8("abcdef"),Utf8("def")) |
+------------------------------------+
| 4 |
+------------------------------------+

substr

Extracts a substring of a specified number of characters from a specific starting position in a string.

substr(str, start_pos[, length])
ParametersDescription
strString expression to repeat.Can be a constant, column, or function, and any combination of string operators.
start_posCharacter position to start the substring at.The first character in the string has a position of 1.
lengthNumber of characters to extract.If not specified, returns the rest of the string after the start position.
View substr Example
SELECT substr('abcdef', 4, 3);
+------------------------------------------+
| substr(Utf8("abcdef"),Int64(4),Int64(3)) |
+------------------------------------------+
| def |
+------------------------------------------+

to_hex

Converts an integer to a hexadecimal string.

to_hex(int)
ParametersDescription
intInteger expression to convert.Can be a constant, column, or function, and any combination of arithmetic operators.
View to_hex Example
SELECT to_hex(100);
+--------------------+
| to_hex(Int64(100)) |
+--------------------+
| 64 |
+--------------------+

translate

Translates characters in a string to specified translation characters.

translate(str, chars, translation)
ParametersDescription
strString expression to operate on.Can be a constant, column, or function, and any combination of string operators.
charCharacters to translate.
translationTranslation characters.Translation characters replace only characters at the same position in the chars string.
View translate Example
SELECT translate('aaabbb', 'bbb', 'ccc');
+---------------------------------------------------+
| translate(Utf8("aaabbb"),Utf8("bbb"),Utf8("ccc")) |
+---------------------------------------------------+
| aaaccc |
+---------------------------------------------------+

trim

The function removes the string consisting of the specified string from the beginning, end or both ends of the specified string.

trim( [ BOTH | LEADING | TRAILING ] [trim_chars FROM ] str )
ParametersDescription
strString expression to operate on.Can be a constant, column, or function, and any combination of string operators.
trim_charsSpecify the characters to remove.
BOTHOptional, means removing specified characters from both ends of the string.
LEADINGOptional, means removing only the specified characters on the left side of the string.
TRAILINGOptional, means removing only the specified characters on the right side of the string.
View trim Example

Remove characters from both sides.

SELECT trim(BOTH 'x' FROM 'xxxHello CnosDBxxx');
+---------------------------------------------+
| btrim(Utf8("xxxHello CnosDBxxx"),Utf8("x")) |
+---------------------------------------------+
| Hello CnosDB |
+---------------------------------------------+

Remove characters from the left.

SELECT trim(LEADING 'x' FROM 'xxxHello CnosDBxxx');
+---------------------------------------------+
| ltrim(Utf8("xxxHello CnosDBxxx"),Utf8("x")) |
+---------------------------------------------+
| Hello CnosDBxxx |
+---------------------------------------------+

Remove characters from the right.

SELECT trim(TRAILING 'x' FROM 'xxxHello CnosDBxxx');
+---------------------------------------------+
| rtrim(Utf8("xxxHello CnosDBxxx"),Utf8("x")) |
+---------------------------------------------+
| xxxHello CnosDB |
+---------------------------------------------+

Remove spaces directly.

SELECT trim('   Hello CnosDB   ');
+----------------------------------+
| trim(Utf8(" Hello CnosDB ")) |
+----------------------------------+
| Hello CnosDB |
+----------------------------------+

Related functions: btrim, ltrim, rtrim

upper

Converts a string to upper-case.

upper(str)
ParametersDescription
strString expression to operate on.Can be a constant, column, or function, and any combination of string operators.
View substr Example
SELECT upper('cnosdb');
+-----------------------+
| upper(Utf8("cnosdb")) |
+-----------------------+
| CNOSDB |
+-----------------------+

Related functions:**initcap, lower

uuid

Returns UUID v4 string value which is unique per row.

uuid()
View uuid Example
SELECT uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| cdc025fe-99c0-40ec-892e-dee3eb23019c |
+--------------------------------------+