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)

Parameters	Description
str	String 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)

Parameters	Description
str	String 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])

Parameters	Description
str	String expression to operate on.Can be a constant, column, or function, and any combination of string operators.
trim_str	String 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])

Parameters	Description
separator	Separator used to insert between concatenated strings.
str	String expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
str_n	Subsequent 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])

Parameters	Description
separator	Separator used to insert between concatenated strings.
str	String expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
str_n	Subsequent 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)

Parameters	Description
expression	Expression 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)

Parameters	Description
str	Can 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:lower，upper

instr

Alias of strpos.

left

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

left(str, n)

Parameters	Description
str	String expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
n	Number 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])

Parameters	Description
str	String expression to concatenate.Can be a constant, column, or function, and any combination of string operators.
n	String length to pad to.
padding_str	String 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])

Parameters	Description
str	String expression to operate on.Can be a constant, column, or function, and any combination of string operators.
trim_str	String 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)

Parameters	Description
str	String expression to repeat.Can be a constant, column, or function, and any combination of string operators.
n	Number 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)

Parameters	Description
str	String expression to repeat.Can be a constant, column, or function, and any combination of string operators.
substr	Substring expression to replace in the input string.Can be a constant, column, or function, and any combination of string operators.
replacement	Replacement 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                  |
+------------------------+

right

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

right(str, n)

Parameters	Description
str	String expression to repeat.Can be a constant, column, or function, and any combination of string operators.
n	Number 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])

Parameters	Description
str	String expression to repeat.Can be a constant, column, or function, and any combination of string operators.
n	String length to pad to.
padding_str	String 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)

Parameters	Description
str	String expression to repeat.Can be a constant, column, or function, and any combination of string operators.
delimiter	String or character to split on.
pos	Position 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])

Parameters	Description
str	String expression to repeat.Can be a constant, column, or function, and any combination of string operators.
start_pos	Character position to start the substring at.The first character in the string has a position of 1.
length	Number 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)

Parameters	Description
int	Integer 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)

Parameters	Description
str	String expression to operate on.Can be a constant, column, or function, and any combination of string operators.
char	Characters to translate.
translation	Translation 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 )

Parameters	Description
str	String expression to operate on.Can be a constant, column, or function, and any combination of string operators.
trim_chars	Specify the characters to remove.
BOTH	Optional, means removing specified characters from both ends of the string.
LEADING	Optional, means removing only the specified characters on the left side of the string.
TRAILING	Optional, 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)

Parameters	Description
str	String 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 |
+--------------------------------------+