CHARACTER_LENGTH #

Returns the length of the String (in Unicode code points, not characters), assuming the String string contains a set of bytes that make up UTF-8 encoded text. If this assumption is not met, the function returns some result (no exception is thrown).
Syntax

CHARACTER_LENGTH(string)

Parameter

• string – String.

Return value

• The length of the String in Unicode code points.

Type: UInt64
Example

select CHARACTER_LENGTH('abcdef123')

Result:

┌─CHARACTER_LENGTH('abcdef123')─┐ 
│ 9                             │ 
└───────────────────────────────┘

CHAR_LENGTH #

Returns the length of the String (in Unicode code points, not characters), assuming the String string contains a set of bytes that make up UTF-8 encoded text. If this assumption is not met, the function returns some result (no exception is thrown).
Syntax

CHAR_LENGTH(s)

Parameter

• s – String.

Return value

• The length of the String in Unicode code points.

Type: UInt64
Example

SELECT CHAR_LENGTH('abcdef123')

Result:

┌─CHAR_LENGTH('abcdef123')─┐ 
│ 9                        │ 
└──────────────────────────┘

ASCII #

Returns the ASCII value of the leftmost character of the String.
Syntax

SELECT ascii('2');

Parameter

• varchar – String

Return value

• bigint

Example

SELECT ascii('dx');

+-------------+ 
| ascii('dx') | 
+-------------+ 
|         100 | 
+-------------+

bin #

Returns the binary String of N. If N is null, the result is NULL.
Syntax

SELECT bin(12);

Parameter

• BIGINT

Return value

• VARCHAR

alphaTokens #

Selects substrings of consecutive bytes from the ranges a–z and A–Z. Returns an array of substrings.
Syntax

alphaTokens(string)

Parameter

• string – String.

Return value

• Array of substrings.

Example

SELECT alphaTokens('abca1abc');

┌─alphaTokens('abca1abc')─┐ 
│ [abca, abc]             │ 
└─────────────────────────┘

appendTrailingCharIfAbsent #

If the String is not empty and does not end with character, character will be appended to the end. charactercharacter
Syntax

appendTrailingCharIfAbsent(string, character)

Parameter

• string – String.
• character – Character.

Return value

• The String with the character appended if absent.

Type: string
Example

appendTrailingCharIfAbsent('abc', '2');

Result:

┌─appendTrailingCharIfAbsent('abc', '2')─┐ 
│ abc2                                   │ 
└────────────────────────────────────────┘

arrayStringConcat #

Concatenates the Strings listed in the array using the separator. separator is an optional parameter: a constant String. The default is an empty string.
Syntax

arrayStringConcat(array[, separator])

Parameter

• array – Array.
• separator – Optional, separator.

Return value

• The concatenated String.

Type: string
Example

SELECT arrayStringConcat(['abc','123']);

Result:

┌─arrayStringConcat(['abc', '123'])─┐ 
│ abc123                            │ 
└───────────────────────────────────┘

Other example

SELECT arrayStringConcat(['abc', '123'], ',')

Result:

┌─arrayStringConcat(['abc', '123'], ',')─┐ 
│ abc,123                                │ 
└────────────────────────────────────────┘

base64Decode #

Decodes the base64-encoded string to the original String. If decoding fails, an exception is thrown.
Alias: FROM_BASE64.
Syntax

base64Decode(string)

Parameter

• string – String.

Return value

• The decoded String.

Type: string
Example

SELECT base64Decode('SGVsbG8gQmFzZTY0');

Result:

┌─base64Decode('SGVsbG8gQmFzZTY0')─┐ 
│ Hello Base64                     │ 
└──────────────────────────────────┘

base64Encode #

Encodes string as base64.
Alias: TO_BASE64.
Syntax

base64Encode(string)

Parameter

• string – String.

Return value

• The encoded String.

Type: string
Example

SELECT base64Encode('Hello Base64')

Result:

┌─base64Encode('Hello Base64')─┐ 
│ SGVsbG8gQmFzZTY0             │ 
└──────────────────────────────┘

bit_length #

Returns the length of the String str in bits.
Syntax

bit_length(str)

Param

• VARCHAR

Return value

• BIGINT

Example

SELECT bit_length('text');

+--------------------+ 
| bit_length('text') | 
+--------------------+ 
|                 32 | 
+--------------------+

char #

Returns a String formed by the ASCII characters corresponding to the decimal values of the Integers N1, N2, and others.
Syntax

char(N1, N2, ...)

Param

• BIGINT

Return value

• VARBINARY

Example

SELECT char(97,110,97,108,121,116,105,99);

┌─char(97, 110, 97, 108, 121, 116, 105, 99)─┐ 
│ analytic                                  │ 
└───────────────────────────────────────────┘

concat #

Joins the parameter strings without a separator.
Syntax

concat(s1, s2, ...)

Param

• s1, s2... – String or Fixed String type value.

Return value

• The string generated by joining the parameters.

If any parameter value is NULL, concat returns NULL.
Example

SELECT concat('Hello, ', 'World!');

Result:

┌─concat('Hello, ', 'World!')─┐ 
│ Hello, World! │ 
└─────────────────────────────┘

concat_ws #

String join operation, the first parameter separator is the separator for the remaining parameters; any string with a value of null will be skipped during joining.
Syntax

concat_ws(separator, str 1, …, str n)

Param

• VARCHAR

Return value

• VARCHAR

Example

SELECT concat_ws(',', 'First name', 'Second name', 'Last Name')AS result;

┌─result───────────────────────────┐ 
│ First name,Second name,Last Name │ 
└──────────────────────────────────┘

concatAssumeInjective #

Similar to concat, except you need to ensure that concat(s1, s2, ...) → sn is injective, which is used for GROUP BY optimization.
If the function always returns different results for different parameter values, the function is called "injective". In other words: different parameters will never produce the same result.
Syntax

concatAssumeInjective(s1, s2, ...)

Param

• s1, s2... – String or Fixed String type value.

Return value

• The string obtained after joining the parameters.

Caution: If any parameter value is NULL, concatAssumeInjective returns NULL.
Example 1

CREATE TABLE test.key_val(key1 String, key2 String, `value` UInt32) ENGINE = CnchMergeTree ORDER BY key2; 
INSERT INTO test.key_val VALUES ('Hello, ','World',1), ('Hello, ','World',2), ('Hello, ','World!',3), ('Hello',', World!',2); 
SELECT * from test.key_val;

┌─key1───┬─key2─────┬─value─┐ 
│ Hello  │ , World! │ 2     │ 
│ Hello, │ World    │ 1     │ 
│ Hello, │ World    │ 2     │ 
│ Hello, │ World! │ 3     │ 
└────────┴──────────┴───────┘

Example 2

SELECT concat(key1, key2), sum(value) FROM test.key_val GROUP BY concatAssumeInjective(key1, key2);

┌─concat(key1, key2)─┬─sum(value)─┐ 
│ Hello,World        │ 3          │ 
│ Hello,World! │ 3          │ 
│ Hello, World! │ 2          │ 
└────────────────────┴────────────┘

convertCharset #

Returns string converted from encoding from to encoding to.
Syntax

convertCharset(s, from, to)

Param

• string– String.
• from– decode from character encoding type.
• to– encode as character encoding type.

Return value

• to character encoding type.

Example

SELECT base64Encode(toString(convertCharset('abc', 'Unicode', 'UTF-8')))

Result:

┌─base64Encode(toString(convertCharset('abc', 'Unicode', 'UTF-8')))─┐ 
│ 5oWi77+9                                                          │ 
└───────────────────────────────────────────────────────────────────┘

count #

Calculates the number of rows or the number of non-NULL values.
Syntax
CNCH supports the following syntax for count:

• count(expr) or COUNT(DISTINCT expr).
• count() or COUNT(*).

Param
This function accepts:

• Zero parameters.
• Zero expressions.

Return value

• If the function is called without parameters, it calculates the number of rows.
• If an expression is passed, the function calculates the number of times this expression returns a non-null value. If the expression returns a Nullable type value, the result of count remains not null. If the expression returns Nullable for all rows, the function returns 0. NULL
• In both cases, the return value type is UInt64.

Details
CNCH supports the COUNT(DISTINCT ...) syntax. The behavior of this construct depends on the count_distinct_implementation settings. It defines which uniq* function is used to perform actions. The default is the uniqExact function.
This SELECT count() FROM table query is not optimized, because the number of entries in the table is not stored separately. It selects a small column from the table and calculates the number of values in it.
However, the query can be optimized by enabling the optimize_functions_to_subcolumns setting for SELECT count(nullable_column) FROM table. When using this function, only the empty subcolumn is read, instead of reading and processing the entire column data. The query is converted to: optimize_functions_to_subcolumns = 1SELECT count(n) FROM tableSELECT sum(NOT n.null) FROM table
Examples
Example 1:

CREATE TABLE test.test_count (id Int32) ENGINE = CnchMergeTree ORDER BY id; 
INSERT INTO test.test_count(id) VALUES(1),(2),(3),(4),(5),(5); 
select count() from test.test_count;

┌─count()─┐ 
│ 6       │ 
└─────────┘

Example 2:

SELECT name, value FROM system.settings WHERE name = 'count_distinct_implementation' 
sql

┌─name──────────────────────────┬─value─────┐ 
│ count_distinct_implementation │ uniqExact │ 
└───────────────────────────────┴───────────┘

select count(distinct(id)) from test.test_count

┌─uniqExact(id)─┐ 
│ 5             │ 
└───────────────┘

This example shows the function run according to the settings count(DISTINCT num). uniqExactcount_distinct_implementation

e #

Returns a Float64 number close to the number e.
Syntax

e()

Parameters

• Not applicable

Return value

• Returns a Float64 number close to the number e.

Type: Float64
Example

SELECT e();

Result:

┌─e()───────────────────┐ 
│ 2.718281828459045e+00 │ 
└───────────────────────┘

elt #

Returns the Nth string.
Syntax

elt(N, str 1, ...,str n);

Parameters

• N is of BIGINT type, str is of VARCHAR type.

Return value

• VARCHAR

Example

SELECT elt(4, 'Aa', 'Bb', 'Cc', 'Dd');

┌─ELT(4, 'Aa', 'Bb', 'Cc', 'Dd')─┐ 
│ Dd                             │ 
└────────────────────────────────┘

empty #

Returns 1 for empty string, returns 0 for non-empty string.
If the string contains at least one byte, even if that byte is a space or an empty byte, it is considered non-empty.
This function also applies to arrays.
Syntax

empty(string)

Parameters

• string– String.

Return value

• Returns 1 for empty string, returns 0 for non-empty string.

Type: UInt8
Example

SELECT empty('');

Result:

┌─empty('')─┐ 
│ 1         │ 
└───────────┘

Other examples

SELECT empty('test')

Result:

┌─empty('12312')─┐ 
│ 0              │ 
└────────────────┘

endsWith #

Returns whether it ends with the specified suffix. If string ends with the specified suffix, then returns 1; otherwise returns 0.
Syntax

endsWith(string, suffix)

Parameters

• string– String.
• suffix– Suffix, tests whether s ends with the specified suffix.

Return value

• If the string ends with the specified suffix, then returns 1; otherwise returns 0.

Type: UInt8
Example

SELECT endsWith('test_end_with','with');

Result:

┌─endsWith('test_end_with', 'with')─┐ 
│ 1                                 │ 
└───────────────────────────────────┘

Other examples

SELECT endsWith('test_end_with','error');

Result:

┌─endsWith('test_end_with', 'error')─┐ 
│ 0                                  │ 
└────────────────────────────────────┘

export_set #

Converts bits to binary values. Of which:

• The system checks the binary value from right to left. If the binary value is 1, it is replaced with the onstr value; if the binary value is 0, it is replaced with the offstr value.
• Return values are separated by separator.
• number_of_bits specifies the number of bits to check, with a default value of 64. If number_of_bits is greater than 64, it will be trimmed to 64 bits; if number_of_bits is -1, the number of bits checked remains at the default value of 64.

Syntax

export_set(bits, onstr, offstr [, separator[,number_of_bits]]);

Parameters

• bits and number_of_bits are both of BIGINT type, onstr, offstr, and separator are all of VARCHAR type.

Return value

• VARCHAR

Example

SELECT export_set(5,'a','b',',',2);

┌─export_set(5, 'a', 'b', ',', 2)─┐ 
│ a,b                             │ 
└─────────────────────────────────┘

extract #

Use a regular expression to extract fragments from a String. If string does not Match the pattern regular expression, Back an Empty string. If the regular expression does Not included a subpattern, extract the fragment that matches the entire regular expression. Otherwise, extract the fragment that matches the first subpattern.
Syntax

extract(haystack, pattern)

Param

• string – String.
• pattern – regular expression Mode

Return value

• Matched String.

Type: string
Example

SELECT extract('abc<regex1>abc<regex2>abc','<.*?>');

As a result:

┌─extract('abc<regex1>abc<regex2>', '<.*?>')─┐ 
│ <regex1>                                   │ 
└────────────────────────────────────────────┘

extractAll #

Use a regular expression to extract all fragments from a String. If string does not Match the pattern regular expression, Back an Empty string. Back a String array composed of all Strings that Match the regular expression. Generally, its Behaviour is the same as the function extract (it Adopt the first subpattern, and if there is no subpattern, it Adopt the entire Expression).
Syntax

extractAll(string, pattern)

Param

• string – String.
• pattern – regular expression Mode

Return value

• Matched String.

Type: string
Example

SELECT extractAll('abc<regex1>abc<regex2>abc','<.*?>');

As a result:

┌─extractAll('abc<regex1>abc<regex2>abc', '<.*?>')─┐ 
│ [<regex1>, <regex2>]                             │ 
└──────────────────────────────────────────────────┘

field #

Back the index Location of str in the List str 1, str 2, str n. If str is not found, Back 0.
Syntax

FIELD(str, str 1, str 2,..., str n);

Param

• VARCHAR

Return value

• BIGINT

Example

SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');

┌─field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff')─┐ 
│                                         2 │ 
└───────────────────────────────────────────┘

find_in_set #

Back the Location of str in the List strlist.
If str is not in strlist or strlist is an Empty string, As a result is 0.
If Either str or strlist Param is null, As a result is null.
Syntax

find_in_set(str, strlist)

Param

• Both str and strlist are of VARCHAR Type.

Return value

• BIGINT

Example

SELECT find_in_set('b','a,b,c,d');

┌─find_in_set('b', 'a,b,c,d')─┐ 
│                           2 │ 
└─────────────────────────────┘

from_base64 #

Decoding the Base64-encoded Param x and Back the result.
Syntax

from_base64(x)

Param

• VARCHAR

Return value

• VARCHAR

Example

SELECT from_base64('Q2hpbmE=');

┌─FROM_BASE64('Q2hpbmE=')─┐ 
│ China                   │ 
└─────────────────────────┘

from_utf8 #

• from_utf8(x): Decoding the UTF-8 encoded x and Back the result.
• from_utf8(x, y): Decoding the Not UTF-8 encoded Param x, and replace it with the specified Illegal character.

Syntax

from_utf8(x) 
from_utf8(x, y)

Param

• VARCHAR

Return value

• VARCHAR

Example

SELECT from_utf8(to_utf8('hello'));

┌─FROM_UTF8(TO_UTF8('hello'))─┐ 
│ hello                       │ 
└─────────────────────────────┘

hex #

Convert the Param x to its corresponding hexadecimal Format String.
Syntax

hex(x)

Param

• BIGINT/VARCHAR

Return value

• VARCHAR

Example

SELECT hex(16);

┌─hex(16)─┐ 
│ 10      │ 
└─────────┘

instr #

Returns the position of the first occurrence of the substring substr in the string str.
Syntax

instr(str, substr)

Parameter

• Both str and substr are of VARCHAR type.

Return value

• BIGINT

Example

SELECT instr('foobarbar', 'bar');

┌─positionCaseInsensitive('foobarbar', 'bar')─┐ 
│                                           4 │ 
└─────────────────────────────────────────────┘

lcase #

Converts ASCII Latin symbols in string to lowercase.
Syntax

lcase(string)

Parameter

• string – String.

Return value

• A string of lowercase letters.

Type: string
Example

SELECT lcase('ABCdef');

Result:

┌─lcase('ABCdef')─┐ 
│ abcdef          │ 
└─────────────────┘

length #

Returns the length of the string as an integer; this function also applies to arrays.
Syntax

length(x)

Parameter

• x – String or array.

Return value

• The length of the string or array.

Type: UInt64
Example

SELECT length('Hello');

Result:

┌─length('Hello')─┐ 
│ 5               │ 
└─────────────────┘

Other examples

SELECT length([1,2,3,4]);

Result:

┌─length([1, 2, 3, 4])─┐ 
│ 4                    │ 
└──────────────────────┘

lengthUTF8 #

Returns the length of the string (as an integer), assuming the string contains a set of bytes forming UTF-8 encoded text. If this assumption is not satisfied, some result is returned (no exception is raised).
Syntax

lengthUTF8(x)

Parameter

• x – String or array.

Return value

• The length of the string or array.

Type: UInt64
Example
Encoding 'Hello test' as UTF-8, we can get \x48\x65\x6C\x6C\x6F\x20\x74\x65\x73\x74

SELECT lengthUTF8('\x48\x65\x6C\x6C\x6F\x20\x74\x65\x73\x74');

Result:

┌─lengthUTF8('Hello test')─┐ 
│ 10                       │ 
└──────────────────────────┘

like #

like(string, pattern), the string LIKE operator checks whether the string matches a simple regular expression.
Syntax

like(string, pattern) 
string LIKE pattern

Parameter

• string – String.
• pattern – a simple regular expression to match.

The regular expression can contain meta-symbols % and _.
% represents any number of any bytes (including zero characters).
_ represents any single byte.
Use a backslash (\) to escape meta-symbols. See the comments in the description of the "match" function regarding escaping.
For regular expressions like %needle%, the code is more optimized and as fast as the position function.
For other regular expressions, the code is the same as the "match" function.
Return value

• 1 or 0.

Type: UInt8
Example

SELECT like('abc','ab')

Type
Result:

┌─like('abc', 'ab')─┐ 
│ 0                 │ 
└───────────────────┘

Other examples

SELECT 'abc' LIKE 'ab%'

Result:

┌─like('abc', 'ab%')─┐ 
│ 1                  │ 
└────────────────────┘

locate #

The locate() function returns the position of the first occurrence of the substring in string.
Alias of position(string, substring).
Syntax

locate(string, substring[, start_pos]) .

Parameter

• string – The string to search for the substring. string.
• substring – The substring to search for. String
• start_pos – Optional parameter, the position of the first character in the string to start searching . UInt

Return value

• The position of the first occurrence of the substring in the string.

Type: UInt64
Example

SELECT locate('Hello World', 'Wor')

Result:

┌─locate('Hello World', 'Wor')─┐ 
│ 7                            │ 
└──────────────────────────────┘

lower #

Converts ASCII Latin symbols in the string to lowercase.
Syntax

lower(string)

Parameter

• string – String.

Return value

• A string in lowercase letters.

Type: string
Example

SELECT lower('ABCdef');

Result:

┌─lower('ABCdef')─┐ 
│ abcdef          │ 
└─────────────────┘

lowerUTF8 #

Converts a string to lowercase, assuming the string contains a sequence of bytes representing UTF-8 encoded text.
Caution: It does not detect the language. Therefore, for Turkish, the result may not be completely correct.
If the length of the UTF-8 byte sequence for the uppercase and lowercase forms of a code point differs, the result for that code point may be incorrect.
If the string contains a sequence of bytes that are not UTF-8, the behavior is undefined.
Syntax

lowerUTF8(s)

Parameters

• s – String.

Return value

• A string in lowercase letters.

Type: string
Example
Encoding 'Hello test' as UTF-8 yields '\x48\x65\x6C\x6C\x6F\x20\x74\x65\x73\x74'.

SELECT lowerUTF8('\x48\x65\x6C\x6C\x6F\x20\x74\x65\x73\x74');

Result:

┌─lowerUTF8('Hello test')─┐ 
│ hello test              │ 
└─────────────────────────┘

lpad #

Prepends padstr to the left of str until the total length reaches len, and returns the resulting string.
If str is longer than len, the return value will be truncated to len characters.
Syntax

lpad(str, len, padstr)

Parameters

• str and padstr are both of type VARCHAR, and len is of type BIGINT

Return value

• VARCHAR

Example

SELECT lpad('Aliyun',9,'#');

┌─leftPad('Aliyun', 9, '#')─┐ 
│ ###Aliyun                 │ 
└───────────────────────────┘

ltrim #

Removes all leading spaces from the string str.
Syntax

ltrim(str)

Parameters

• VARCHAR

Return value

• VARCHAR

Example

SELECT ltrim('  abc');

┌─trimLeft('  abc')─┐ 
│ abc               │ 
└───────────────────┘

make_set #

Returns a set value (a string containing substrings separated by characters), which includes the strings corresponding to the bits that are set.
Syntax

make_set(bits, str 1, str 2,...);

Parameters

• bits is of type BIGINT, str is of type VARCHAR

Return value

• VARCHAR

Example

SELECT make_set(5,'hello','nice','world');

┌─make_set(5, 'hello', 'nice', 'world')─┐ 
│ hello,world                           │ 
└───────────────────────────────────────┘

match #

Checks whether the string matches the pattern regular expression. re2 regular expression. The syntax of the re2 regular expression is more restricted than Perl regular expression syntax.
Caution: The backslash symbol (\) is used for escaping in regular expressions. The same symbol is also used for escaping in string literals. Therefore, to escape a symbol in a regular expression, you must write two backslashes (\\) in the string literal.
The regular expression treats the string as a sequence of bytes. The regular expression cannot contain null bytes.
For patterns that search for substrings in a string, it is better to use LIKE or "position" because they are faster.
Syntax

match(string, pattern)

Parameters

• string– String.
• pattern– Pattern to match.

Return value

• Returns 0 if not matched; returns 1 if matched.

Type: UInt8
Example

SELECT match('abc<regex1>abc<regex2>abc','<.*?>');

Result:

┌─match('abc<regex1>abc<regex2>abc', '<.*?>')─┐ 
│ 1                                           │ 
└─────────────────────────────────────────────┘

mid #

Returns the substring starting from the "offset" index with a length of "length". Character indexes start from 1 (same as standard SQL). The "offset" and "length" parameters must be constants.
Syntax

mid(s, offset, length)

Parameters

• s– String.
• offset– Starting offset of the substring.
• length– Length of the substring.

Return value

• Substring.

Type: string
Example

SELECT mid('Hello CNCH',7,4);

Result:

┌─mid('Hello CNCH', 7, 4)─┐ 
│ CNCH                    │ 
└─────────────────────────┘

multiFuzzyMatchAny #

Same as multiMatchAny, but returns 1 if any pattern matches the string within a constant edit distance. This function is also in experimental mode and may be very slow. For more information, please refer to the hyperscan documentation
Syntax

multiFuzzyMatchAny(string, distance, [pattern_1, pattern_2, …, pattern_n])

Parameters

• string– String.
• distance– Integer; see edit distance.
• pattern– Pattern used for fuzzy matching.

Return value

• Returns 1 if any pattern matches the string; otherwise, returns 0.

Type: UInt8
Example

SELECT multiFuzzyMatchAny('123ab12cdef',2,['abcd']);

Result:

┌─multiFuzzyMatchAny('123ab12cdef', 2, ['abcd'])─┐ 
│ 1                                              │ 
└────────────────────────────────────────────────┘

multiFuzzyMatchAnyIndex #

Same as multiFuzzyMatchAny, but returns the index of any pattern that matches the string within a constant edit distance.
Syntax

multiFuzzyMatchAnyIndex(string, distance, [pattern_1, pattern_2, …, pattern_n])

Parameters

• string– String.
• distance– Integer; see edit distance.
• pattern– Pattern used for fuzzy matching.

Return value

• Returns the index of any pattern that matches the string.

Type: UInt64
Example

SELECT multiFuzzyMatchAnyIndex('111333444CN1CH',1,['abc','def','CNCH']);

Result:

┌─multiFuzzyMatchAnyIndex('111333444CN1CH', 1, ['abc', 'def', 'CNCH'])─┐ 
│ 3                                                                    │ 
└──────────────────────────────────────────────────────────────────────┘

multiMatchAny #

multiMatchAny(haystack，[pattern_1，pattern_2，…，pattern_n]）
Same as match, but returns 0 if no regular expression matches; returns 1 if any pattern matches. It uses the hyperscan library. For patterns searching for substrings in the string, it is recommended to use multiSearchAny because it is faster.
Caution: The length of any string haystack must be less than 2 (32) bytes; otherwise, an exception will be thrown. This limitation is due to the hyperscan API.
Syntax

multiMatchAny(string, [pattern_1, pattern_2, …, pattern_n])

Parameters

• string– String.
• pattern– Pattern to match.

Return value

• Returns 0 if not matched; returns 1 if matched.

Type: UInt8
Example

SELECT multiMatchAny('abc<regex1>abc<regex2>abc',['<.*?>','error_patten']);

Result:

┌─multiMatchAny('abc<regex1>abc<regex2>abc', ['<.*?>', 'error_patten'])─┐ 
│ 1                                                                     │ 
└───────────────────────────────────────────────────────────────────────┘

multiMatchAnyIndex #

Same as multiMatchAny, but returns the index of any pattern that matches the string.
Syntax

multiMatchAnyIndex(string, [pattern_1, pattern_2, …, pattern_n])

Parameters

• string– String.
• pattern– Pattern to match.

Return value

• Returns the index of any pattern that matches the string.

Type: UInt64
Example

SELECT multiMatchAnyIndex('abc<regex1>abc<regex2>abc',['error_patten', '<.*?>']);

Result:

┌─multiMatchAnyIndex('abc<regex1>abc<regex2>abc', ['error_patten', '<.*?>'])─┐ 
│ 2                                                                          │ 
└────────────────────────────────────────────────────────────────────────────┘

multiSearchAllPositions #

Same as position, but returns an Array of the positions of the corresponding substrings found in the string. Position indexes start from 0.
The Search operates on byte sequences and is independent of String encoding and sorting.

• For case-insensitive ASCII Search, use the function multiSearchAllPositionsCaseInsensitive.
• To Search using UTF-8, use the function multiSearchAllPositionsUTF8
• For case-insensitive UTF-8 Search, use the function multiSearchAllPositionsCaseInsensitiveUTF8.

Syntax

multiSearchAllPositions(string, [substring_1, substring_2, ..., Substring_n])

Param

• string— The String in which substrings are searched. String.
• substring— The substring to Search for. String.

Return value

• If the corresponding substring is found, is an array of Start positions (counting from 1); if not found, is 0.

Type: Array(UInt64)
Example

SELECT multiSearchAllPositions('Hello, World!', ['hello', '!', 'world']);

As a result:

┌─multiSearchAllPositions('Hello, World!', ['hello', '!', 'world'])─┐ 
│ [0, 13, 0]                                                        │ 
└───────────────────────────────────────────────────────────────────┘

multiSearchAllPositionsUTF8 #

See multiSearchAllPositions. ## multiSearchAny
If at least one String needle_i string matches the String, Return 1; otherwise, Return 0.
For case-insensitive Search or/and UTF-8 Format, use the functions multiSearchAnyCaseInsensitive, multiSearchAnyUTF8, multiSearchAnyCaseInsensitiveUTF8.

Syntax

multiSearchAny(string, [pattern_1, pattern_2, …, pattern_n])

Param

• string– String.
• pattern– Mode

Return value

• If at least one Mode matches the String, Return 1; string otherwise, Return 0.

Type: UInt8
Example

SELECT multiSearchAny('can_you_find_CNCH?',['abc','CNCH']);

As a result:

┌─multiSearchAny('can_you_find_CNCH?', ['abc', 'CNCH'])─┐ 
│ 1                                                     │ 
└───────────────────────────────────────────────────────┘

multiSearchFirstIndex #

Return the index i (counting from 1) of the Leftmost pattern_i found in the String; otherwise, Return 0.
For case-insensitive Search or/and UTF-8 Format, use the functions multiSearchFirstIndexCaseInsensitive, multiSearchFirstIndexUTF8, multiSearchFirstIndexCaseInsensitiveUTF8.
Syntax

multiSearchFirstIndex(string, [pattern_1, pattern_2, …, pattern_n])

Param

• string– String.
• pattern– Mode

Return value

• Return the index i (counting from 1) of the Leftmost pattern_i found in the String; otherwise, Return 0.

Type: UInt8
Example

SELECT multiSearchFirstIndex('which_pattern_matchs_CNCH?',['abc','CNCH']);

As a result:

┌─multiSearchFirstIndex('which_pattern_matchs_CNCH?', ['abc', 'CNCH'])─┐ 
│ 2                                                                    │ 
└──────────────────────────────────────────────────────────────────────┘

multiSearchFirstPosition #

Same as position, but returns the Leftmost offset of the String that matches some substring.
For case-insensitive Search or/and UTF-8 Format, use the functions multiSearchFirstPositionCaseInsensitive, multiSearchFirstPositionUTF8, multiSearchFirstPositionCaseInsensitiveUTF8.
Syntax

multiSearchFirstPosition(string, [pattern_1, pattern_2, …, pattern_n])

Param

• string– String.
• substring– Substring to Match.

Return value

• Return the index i (counting from 1) of the Leftmost pattern_i found in the String; otherwise, Return 0.

Type: UInt8
Example

SELECT multiSearchFirstPosition('123_abc_abc_CNCH?',['abc','CNCH']);

As a result:

┌─multiSearchFirstPosition('123_abc_abc_CNCH?', ['abc', 'CNCH'])─┐ 
│ 5                                                              │ 
└────────────────────────────────────────────────────────────────┘

ngramDistance #

string Calculate the 4-Yuan Mode distance between string and substring: Calculate the symmetric difference between two 4-Yuan Mode multisets and normalize it by their total cardinality. Returns a Floating-point number between 0 and 1—the closer to Zero, the more similar the Strings are to each other. If the constant substring or substring exceeds 32Kb, an Abnormal is thrown. If certain non-constant substring or substring String exceeds 32Kb, Then the distance is always 1.
For case-insensitive search or/and UTF-8 format, please use the functions ngramDistanceCaseInsensitive, ngramDistanceUTF8, ngramDistanceCaseInsensitiveUTF8.
Syntax

ngramDistance(string, [substring_1, substring_2, …, substring_n]) 
sql

Param

• string– String.
• substring– Substring to match.

Return value

• Back the index i of the leftmost found substring_i (starting from 1), string otherwise Back 0.

Type: Float32
Example

SELECT ngramDistance('abc123ascCNCH_83q','CNCH'):

As a result:

┌─ngramDistance('abc123ascCNCH_83q', 'CNCH')─┐ 
│ 8.666667e-01                               │ 
└────────────────────────────────────────────┘

not #

Back the result of the logical not of the calculated value. Corresponds to logical not operator.
Syntax

not(val); 
sql

Param

• val— Value. Int, UInt, Float or Nullable.

Return value

• 1, if val is 0.
• 0, if val is a non-zero value.
• NULL, if val is a NULL value.

Type: UInt8 or Nullable (UInt8).
Example

SELECT NOT(1);

As a result:

┌─not(1)─┐ 
│ 0      │ 
└────────┘

notEmpty #

If empty, Back 0 string, if non-empty, Back 1 string.
This function also applies to arrays.
Syntax

notEmpty(string)

Param

• s– String.

Return value

• Back 1 for empty string, Back 0 for non-empty string.

Type: UInt8
Example

SELECT notEmpty('test');

As a result:

┌─notEmpty('test')─┐ 
│ 1                │ 
└──────────────────┘

Other example

SELECT notEmpty('')

As a result:

┌─notEmpty('')─┐ 
│ 0            │ 
└──────────────┘

notLike #

notLike(string, pattern), string does not match pattern operator.
Same meaning as "like", but is the negative form.
Syntax

notLike(string, pattern)

Param

• string– String.
• pattern– Pattern to match.

Return value

• If not match, Back 0; if match, Back 1.

Type: UInt8
Example

SELECT notLike('test1','test2');

As a result:

┌─notLike('test1', 'test2')─┐ 
│ 1                         │ 
└───────────────────────────┘

oct #

Back the octal string representation form of integer N.
Syntax

oct(N)

Param

• BIGINT

Return value

• VARCHAR

Example

SELECT oct(12);

┌─oct(12)─┐ 
│ 14      │ 
└─────────┘

ord #

If the leftmost character of string x is a multibyte character, Back the code of that character.
Syntax

ord(x)

Param

• VARCHAR

Return value

• BIGINT

Example

SELECT ord('China');

┌─ord('China')─┐ 
│           67 │ 
└──────────────┘

position #

Search for substring in string.
Back the location of the found substring in the string, starting from 1.
Advisory: Search is default case-sensitive. For case-insensitive search, please use the function positionCaseInsensitive.
Syntax

position(substring IN string) 
position(string, substring[, start_pos])

alias: locate(string, substring[, start_pos]).
Advisory: The syntax position(substring IN string) provides SQL compatibility. This function works the same way as position(string, substring).
Param

• string– String to search for substring. String.
• substring– Substring to search for. String
• start_pos– Optional param, the location of the first character in the string to start search. UInt

Return value

• Back the location of the found substring in the string, starting from 1.

Type: Integer
Example 1
The phrase "Hello, world!" contains a set of bytes representing single-byte encoded text. This function returns some expected results:

SELECT position('Hello, world!', '!');

Result:

┌─position('Hello, world!', '!')─┐ 
│ 13                             │ 
└────────────────────────────────┘

Example 2

SELECT position('Hello, world!', 'o', 1),position('Hello, world!', 'o', 7);

┌─position('Hello, world!', 'o', 1)─┬─position('Hello, world!', 'o', 7)─┐ 
│                                 5 │                                 9 │ 
└───────────────────────────────────┴───────────────────────────────────┘

The same phrase in Russian contains characters that cannot be represented using a single byte. This function returns some unexpected results (use the positionUTF8 function for multi-byte encoded text):

SELECT position('Привет, мир!', '!');

Result:

┌─position('Привет, мир!', '!')─┐ 
│                            21 │ 
└───────────────────────────────┘

position(substring IN string) syntax example

SELECT 3 = position('c' IN 'abc');

Result:

┌─equals(3, position('abc', 'c'))─┐ 
│                               1 │ 
└─────────────────────────────────┘

Example:

SELECT 6 = position('/' IN s) FROM (SELECT 'Hello/World' AS s);

Result:

┌─equals(6, position(s, '/'))─┐ 
│                           1 │ 
└─────────────────────────────┘

positionCaseInsensitive #

Same as position, returns the position of the substring found in the String, starting from 1. Use this function to perform case-insensitive searches.
Assume the String contains a set of bytes representing single-byte encoded text. If this assumption is not satisfied and the characters cannot be represented using a single byte, the function will not throw an exception and will return some unexpected results. If a character can be represented using two bytes, it will use two bytes, and so on.
Syntax

positionCaseInsensitive(string, substring[, start_pos])

Parameters

• string – The String in which to search for the substring. String.
• substring – The substring to search for. String
• start_pos – Optional parameter, the position of the first character in the String to start the search. UInt

Return value

• If the substring is found, the starting position in bytes (counting from 1).
• If the substring is not found, the result is 0.

Type: Integer.
Example

SELECT positionCaseInsensitive('Hello, world!', 'hello');

Result:

┌─positionCaseInsensitive('Hello, world!', 'hello')─┐ 
│ 1                                                 │ 
└───────────────────────────────────────────────────┘

positionCaseInsensitiveUTF8 #

Same as positionUTF8, but case-insensitive. Returns the position of substring found in string (in Unicode points), starting from 1.
Works under the assumption that the String contains a set of bytes representing UTF-8 encoded text. If this assumption is not satisfied, the function will not throw an exception and will return some unexpected results. If a character can be represented using two Unicode points, it will use two, and so on.
Syntax

positionCaseInsensitiveUTF8(string, substring[, start_pos])

Parameters

• string – The String in which to search for the substring. String.
• substring – The substring to search for. String
• start_pos – Optional parameter, the position of the first character in the String to start the search. UInt

Return value

• If the substring is found, the starting position in Unicode points (counting from 1).
• If the substring is not found, the value is 0.

Type: Integer.
Example
Query:

SELECT positionCaseInsensitiveUTF8('Привет, мир!', 'Мир');

Result:

┌─positionCaseInsensitiveUTF8('Привет, мир!', 'Мир')─┐ 
│ 9                                                  │ 
└────────────────────────────────────────────────────┘

positionUTF8 #

Returns the position (in Unicode code points) of the substring found in the string, starting from 1.
Operates under the assumption that the String contains a set of bytes representing UTF-8 encoded text. If this assumption is not met, the function does not throw an exception and returns some unexpected results. If a character can be represented using two Unicode code points, it will use two, and so on.
For case-insensitive searches, use the function positionCaseInsensitiveUTF8.
Syntax

positionUTF8(string, substring[, start_pos])

Parameters

• string– The string in which to search for the substring. String.
• substring– The substring to search for. String
• start_pos– Optional parameter, the position of the first character in the string from which to start searching. UInt

Return value

• If the substring is found, returns the starting position in Unicode code points (counting from 1).
• If the substring is not found, the value is 0.

Type: Integer.
Example

SELECT positionUTF8('Привет, мир!', '!') as example

The phrase "Hello, world!" contains a set of Unicode code points representing single-point encoded text. This function returns the expected result:
Result:

┌─example─┐ 
│ 12      │ 
└─────────┘

regexpExtract #

The regexpExtract function extracts the first substring from the target string (haystack) that matches the regular expression (regexp) pattern and corresponds to the specified regular group index.
Syntax

regexpExtract(haystack, pattern[, index])

Alias: REGEXP_EXTRACT
Parameter description

• haystack — The target string to match the regular expression pattern. Type: String.
• pattern — The regular expression string. pattern can contain multiple regular groups; index specifies which group to extract. An index of 0 means the entire regular expression is matched. Type: const String.
• index — Optional parameter, a non-negative integer (≥0), default value is 1, used to specify the regular group number to extract. Type: (U)Int*.

Return value
This function returns a string (String).
Usage example
Query:

SELECT 
    regexpExtract('100-200', '(\d+)-(\d+)', 1), 
    regexpExtract('100-200', '(\d+)-(\d+)', 2), 
    regexpExtract('100-200', '(\d+)-(\d+)', 0), 
    regexpExtract('100-200', '(\d+)-(\d+)');

Output:

┌─regexpExtract('100-200', '(\d+)-(\d+)', 1)─┬─regexpExtract('100-200', '(\d+)-(\d+)', 2)─┬─regexpExtract('100-200', '(\d+)-(\d+)', 0)─┬─regexpExtract('100-200', '(\d+)-(\d+)')─┐ 
│ 100                                          │ 200                                          │ 100-200                                      │ 100                                       │ 
└──────────────────────────────────────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┴───────────────────────────────────────────┘

regexpQuoteMeta #

This function adds a backslash before certain predefined characters in the string.
Predefined characters: \0, |, (, ), ^, $, ., [, ], ?, *, +, {, :, -. |()^$.[]?*+{:-
This implementation is slightly different from re2::RE2::QuoteMeta. It escapes zero bytes as \0 instead of \x00, and only escapes the necessary characters.
Syntax

regexpQuoteMeta(string)

Parameters

• string– String.

Return value

• String.

Type: string
Example

SELECT regexpQuoteMeta(' | , ( , ) , ^ , $ , . , [ , ] , ? ,');

Result:

┌─regexpQuoteMeta(' | , ( , ) , ^ , $ , . , [ , ] , ? ,')─┐ 
│  \| , ( , ) , ^ , $ , . , [ , ] , ? ,           │ 
└─────────────────────────────────────────────────────────┘

regexp/rlike #

Perform regular expression matching between the String expression and pattern. If the match is successful, return 1; otherwise, return 0.
Syntax

expression RLIKE pattern; 
expression REGEXP pattern;

Parameters

• VARCHAR

Return value

• BOOLEAN

Example

SELECT 'Michael!' REGEXP '.*';

┌─rlike('Michael!', '.*')─┐ 
│                       1 │ 
└─────────────────────────┘

rpad #

Append padstr to the right side of the String str until its length reaches len, and return the concatenated string.
Syntax

rpad(str, len, padstr)

Parameters

• Both str and padstr are of VARCHAR type, and len is of BIGINT type.

Return value

• VARCHAR

Example

SELECT rpad('abc',9,'#');

┌─rightPad('abc', 9, '#')─┐ 
│ abc######               │ 
└─────────────────────────┘

repeat #

Return a string composed of the String str repeated count times.
Syntax

repeat(str, count);

Parameters

• str is of VARCHAR type, and count is of BIGINT type.

Return value

• VARCHAR

Example

SELECT repeat('a', 3);

┌─repeat('a', 3)─┐ 
│ aaa            │ 
└────────────────┘

replace #

Replace all occurrences of substring with . stringreplacement
Alias of replaceAll
Syntax

replace(string, substring, replacement)

Parameters

• string— The string in which to search for substrings.
• substring— The substring to search for.
• replacement— Use the replacement string to replace the matched substring.

Return value

• Replace all occurrences of substring with . stringreplacement

Type: string
Example

SELECT replace('test target_string test','target_string','CNCH');

Result:

┌─replace('test target_string test', 'target_string', 'CNCH')─┐ 
│ test CNCH test                                              │ 
└─────────────────────────────────────────────────────────────┘

replaceAll #

Replace all occurrences of substring with . stringreplacement
Please refer to replac

replaceOne #

If present, replace the first occurrence of substring in with . stringreplacement
Syntax

replaceOne(string, substring, replacement)

Parameters

• string– The string in which to search for substrings. string。
• substring– The substring to search for. string
• start_pos– Optional parameter, the position of the first character in the string to start the search. UInt

Return value

• Replace the first occurrence of the substring in the string with the replacement value.

Type: string
Example

SELECT replaceOne('test target_string test target_string','target_string','CNCH');

Result:

┌─replaceOne('test target_string test target_string', 'target_string', 'CNCH')─┐ 
│ test CNCH test target_string                                                 │ 
└──────────────────────────────────────────────────────────────────────────────┘

replaceRegexpAll #

This has the same effect as replaceAll, but uses a regular expression.

• The pattern can be specified as "replacement". This pattern can contain replacement \0-\9.
• Replacement \0 includes the entire regular expression. Replacement \1-\9 corresponds to the subpattern number. To \ use this character in the template, escape it with \.

Syntax

replaceRegexpAll(string, pattern, replacement)

Parameters

• string– String.
• pattern– Matching pattern.
• replacement— Use the replacement string to replace the matched substring.

Return value

• Replace all occurrences of the substring in the string with the replacement value.

Type: string
Example

SELECT replaceRegexpAll('Hello, World!', '.', '\0\0');

┌─replaceRegexpAll('Hello, World!', '.', '\0\0')─┐ 
│ HHeelllloo,,  WWoorrlldd!! │ 
└──────────────────────────────────────────────────┘

The exception is that if the regular expression acts on an empty substring, multiple replacements are not performed.
Example:

SELECT replaceRegexpAll('Hello, World!', '^', 'here: ');

┌─replaceRegexpAll('Hello, World!', '^', 'here: ')─┐ 
│ here: Hello, World! │ 
└──────────────────────────────────────────────────┘

replaceRegexpOne #

If present, only the first match is replaced.
Caution: Use pattern regular expressions for replacement. re2 regular expression.
The pattern can be specified as "replacement". This pattern can contain replacement\0-\9.
Replacement\0 includes the entire regular expression. Replacement\1-\9 corresponds to the subpattern number. To\ use this character in the template, escape it with\.
Also remember that string literals require additional escaping.
Syntax

replaceRegexpOne(string, pattern, replacement)

Parameters

• string – String.
• pattern – Matching pattern.
• replacement — Uses the replacement string to replace the matched substring.

Return value

• Replaces the first occurrence of the matched substring in the string with the replacement content.

Type: string
Example

SELECT replaceRegexpOne('test <target_string> test <target_string>','<.*?>','CNCH');

Result:

┌─replaceRegexpOne('test <target_string> test <target_string>', '<.*?>', 'CNCH')─┐ 
│ test CNCH test <target_string>                                                 │ 
└────────────────────────────────────────────────────────────────────────────────┘

Other examples
Example 1. Convert date to US format:

select '2014-03-17', replaceRegexpOne(toString('2014-03-17'), '(\d{4})-(\d{2})-(\d{2})', '\2/\3/\1')

┌─'2014-03-17'─┬─replaceRegexpOne(toString('2014-03-17'), '(\d{4})-(\d{2})-(\d{2})', '\2/\3/\1')─┐ 
│ 2014-03-17   │ 03/17/2014                                                                            │ 
└──────────────┴───────────────────────────────────────────────────────────────────────────────────────┘

Example 2. Copy the string ten times:

SELECT replaceRegexpOne('Hello, World!', '.*', '\0\0\0\0\0\0\0\0\0\0');

Result:

┌─replaceRegexpOne('Hello, World!', '.*', '\0\0\0\0\0\0\0\0\0\0')──────────────────────────────────────────────────────────┐ 
│ Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World! │ 
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

reverse #

Reverses the string (as a byte sequence).
Syntax

reverse(string)

Parameters

• string – String.

Return value

• Reversed string.

Type: string
Example

SELECT reverse('abcd1234')

Result:

┌─reverse('abcd1234')─┐ 
│ 4321dcba            │ 
└─────────────────────┘

reverseUTF8 #

Reverses the Unicode code point sequence, assuming the string contains a set of bytes representing UTF-8 text. Otherwise, it performs other actions (no exception is thrown).
Syntax

reverseUTF8(string)

Parameters

• string – UTF8 string.

Return value

• Reversed string.

Type: string
Example

SELECT reverseUTF8('\x61\x62\x63\x64\x31\x32\x33\x34')

Result:

┌─reverseUTF8('abcd1234')─┐ 
│ 4321dcba                │ 
└─────────────────────────┘

size #

Returns the length of the string in bytes (not in characters or code points).

Note: This function also applies to arrays.

Alias for length
Syntax

size(x)

Parameters

• x – String or array.

Return value

• The length of the string or array.

Type: UInt64
Example

SELECT size('Hello');

Result:

┌─size('Hello')─┐ 
│ 5             │ 
└───────────────┘

Other examples

SELECT size([1,2,3,4]);

Result:

┌─size([1, 2, 3, 4])─┐ 
│ 4                  │ 
└────────────────────┘

splitByChar #

Splits the string into substrings separated by the specified character. It uses aseparator which is a constant string consisting of a single character.
Returns an array of selected substrings. If the separator appears at the beginning or end of the string, or if there are multiple consecutive separators, empty substrings may be selected.
Syntax

splitByChar(separator, string) 
sql

Parameters

• separator — The separator must contain exactly one character. string.
• string — The string to be split. string.

Return value
Returns an array of selected substrings. Empty substrings may be selected in the following cases:

• The separator appears at the beginning or end of the String;
• There are multiple consecutive separators;
• The original String string is empty.

Type: array
Example

SELECT splitByChar(',', '1,2,3,abcde'); 
sql

As a result:

┌─splitByChar(',', '1,2,3,abcde')─┐ 
│ [1, 2, 3, abcde]                │ 
└─────────────────────────────────┘ 
text

splitByString #

Splits the String into substrings separated by a String. It uses the constant String separator with multiple characters as the separator. If the String separator is empty, it splits string into an array of single characters.
Syntax

splitByString(separator, string)

Param

• separator— Should contain the separator of the String. String.
• string— the string to split. String.

Return value
Returns an array of selected substrings. Empty substrings may be selected in the following cases:

• The separator appears at the beginning or end of the String;
• There are multiple consecutive separators;
• The original String string is empty.

Type: array
Example

SELECT splitByString('test', 'abctestCNCHtestdef')

As a result:

┌─splitByString('test', 'abctestCNCHtestdef')─┐ 
│ [abc, CNCH, def]                            │ 
└─────────────────────────────────────────────┘

startsWith #

Returns 1 if string starts with the specified prefix, otherwise returns 0.
Syntax

startsWith(string, prefix)

Param

• string— String.
• prefix— Match prefix.

Return value

• 1, if the string starts with the specified prefix.
• 0, if the string does not start with the specified prefix.

Type: UInt8
Example

SELECT startsWith('Hello, world!', 'He');

As a result:

┌─startsWith('Hello, world!', 'He')─┐ 
│ 1                                 │ 
└───────────────────────────────────┘

strcmp #

If str 1 and str 2 are identical, the result is 0. If str 1 is smaller than str 2 according to the current sort order, the result is -1; otherwise, the result is 1.
Syntax

strcmp(str 1, str 2);

Param

• str 1 and str 2 are both of VARCHAR type.

Return value

• BIGINT

Example

SELECT strcmp('text', 'text2');

┌─strcmp('text', 'text2')─┐ 
│                      -1 │ 
└─────────────────────────┘

substr #

See substring
Returns a substring starting at the byte index offset with a length of length bytes. Character indexes start at one (same as standard SQL).
Caution: The offset and length parameters must be constants.
Syntax

substring(string, offset, length)

Param

• string– String.
• offset– Start offset of the substring.
• length– Length of the substring.

Return value

• Substring.

Type: string
Example

SELECT substring('Hello CNCH', 7, 4)

As a result:

┌─substr('Hello CNCH', 7, 4)─┐ 
│ CNCH                       │ 
└────────────────────────────┘

substringUTF8 #

Same as substring, but operates on Unicode code points. Assumes the String contains a set of bytes representing UTF-8 encoded text.

Syntax

substringUTF8(string, offset, length)

Param

• string– String.
• offset– Start offset of the substring.
• length– Length of the substring.

Return value

• Substring.

Type: string
Example

SELECT substringUTF8('\x48\x65\x6c\x6c\x6f\x20\x43\x4e\x43\x48H', 7, 4)

Result:

┌─substringUTF8('Hello CNCHH', 7, 4)─┐ 
│ CNCH                               │ 
└────────────────────────────────────┘

trimBoth #

Removes all consecutive spaces (ASCII character 32) from both ends of the string. It does not remove other types of space characters (tab, non-breaking space, and others).
Syntax

trimBoth(string)

Alias: trim(string).
Param

• string— String to be trimmed. String.

Return value

• String without leading and trailing spaces.

Type: String
Example

SELECT trimBoth('     Hello, world!     ') as a;

Result:

┌─a─────────────┐ 
│ Hello, world! │ 
└───────────────┘

trimLeft #

Removes all consecutive spaces (ASCII character 32) from the beginning of the string. It does not remove other types of space characters (tab, non-breaking space, and others).
Syntax

trimLeft(string)

Alias: trim(string).
Param

• string— String to be trimmed. String.

Return value

• String without leading spaces.

Type: String
Example

SELECT trimLeft('     Hello, world!     ') as example

Result:

┌─example────────────┐ 
│ Hello, world! │ 
└────────────────────┘

trimRight #

Removes all consecutive spaces (ASCII character 32) from the end of the string. It does not remove other types of space characters (tab, non-breaking space, and others).
Syntax

trimRight(string)

Alias: trim(string).
Param

• string— String to be trimmed. String.

Return value

• String without trailing spaces.

Type: String
Example

SELECT trimRight('     Hello, world!     ');

Result:

┌─trimLeft('     Hello, world!     ')─┐ 
│ Hello, world! │ 
└─────────────────────────────────────┘

tryBase64Decode #

Similar to base64Decode, but if an error occurs, an empty string is returned.
Syntax

tryBase64Decode(string)

Param

• string– base64 encoded string.

Return value

• Decoded string.

Type: string
Example

SELECT tryBase64Decode('SGVsbG8gV29ybGQh');

Result:

┌─tryBase64Decode('SGVsbG8gV29ybGQh')─┐ 
│ Hello World! │ 
└─────────────────────────────────────┘

ucase #

Converts ASCII Latin symbols in the string to uppercase.
Syntax

ucase(string)

Param

• string– String.

Return value

• String in uppercase letters.

Type: string
Example

SELECT ucase('ABCdef');

Result:

┌─ucase('ABCdef')─┐ 
│ ABCDEF          │ 
└─────────────────┘

upper #

Converts ASCII Latin symbols in the string to uppercase.
Syntax