Text functions

This page describes the available functions to assist with performing text manipulation such as concatenation, case conversion, string length calculation, and pattern matching via regular expressions.

concat#

concat(str, ...) - concatenates a string from one or more input values.

Example

SELECT firstName, lastName, concat(firstName, ' ', lastName) FROM names;

firstName	lastName	concat
Tim	Thompson	Tim Thompson
Anna	Thompson	Anna Thompson
Anna	Mason	Anna Mason
Tom	Johnson	Tom Johnson
Tim	Smith	Tim Smith

tip

concat() can be used to generate line protocol. See an example below.

Generating line protocol

SELECT
concat(
    'trades,instrument=', rnd_str(2,2,0),
    ',side=', rnd_str('B', 'S'),
    ' price=', abs(cast(rnd_double(0)*100000 AS INT)),
    ',quantity=', abs(cast(rnd_double(0)*10000 AS INT)),
    ' ',
    1571270400000 + (x-1) * 100
)
FROM long_sequence(5) x;

trades,instrument=CR,side=B price=70867,quantity=9192 1571270400000
trades,instrument=LN,side=S price=37950,quantity=1439 1571270400100
trades,instrument=ZJ,side=S price=82829,quantity=8871 1571270400200
trades,instrument=EW,side=S price=10427,quantity=1945 1571270400300
trades,instrument=MI,side=B price=99348,quantity=8450 1571270400400

length#

length(string) - reads length of string value type (result is int)

length(symbol) - reads length of symbol value type (result is int)

length(blob) - reads length of binary value type (result is long)

a string
a symbol
a binary blob

Example

SELECT name a, length(name) b FROM names limit 4

a	b
AARON	5
AMELIE	6
TOM	3
null	-1

left#

left(string, count) - extracts a substring of the given length from a string (starting from left).

Arguments:

string is a string to extract from.
count is an integer specifying the count of characters to be extracted into a substring.

Return value:

Returns a string with the extracted characters.

Examples:

Example

SELECT name, left('Thompson', 3) l FROM names LIMIT 3

name	l
AARON	AAR
AMELIE	AME
TOM	TOM

right#

right(string, count) - extracts a substring of the given length from a string (starting from right).

Arguments:

string is a string to extract from.
count is an integer specifying the count of characters to be extracted into a substring.

Return value:

Returns a string with the extracted characters.

Examples:

Example

SELECT name, right('Thompson', 2) r FROM names LIMIT 3

name	l
AARON	ON
AMELIE	IE
TOM	OM

strpos#

strpos(string, substring) - searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. The performed search is case-sensitive.

Arguments:

string is a string to search in.
substring is a string to search for.

Return value:

Returns an integer for the substring position. Positions start from 1.

Examples:

Example

SELECT name, strpos(name, 'Thompson') idx FROM full_names LIMIT 4

name	idx
Tim Thompson	5
Anna Thompson	6
Anna Mason	0
Tom Johnson	0

Assuming we have a table example_table with a single string type column col:

col
apple,pear
cat,dog
...

As a more advanced example, we can use strpos() to split the string values of col, in this case splitting at the comma , character. By using left()/right() functions, we can choose the string values at the left and right of the comma:

Splitting string into two separate columns

SELECT col,
       left(col, strpos(col, ',') - 1) as col1,
       right(col, length(col) - strpos(col, ',')) as col2
FROM example_table;

col	col1	col2
apple,pear	apple	pear
cat,dog	cat	dog

substring#

substring(string, start, length) - extracts a substring from the given string.

Arguments:

string is a string to extract from.
start is an integer specifying the position of the first character to be extracted. Positions start from 1.
length is an integer specifying the count of characters to be extracted. Should be non-negative.

Return value:

Returns a string with the extracted characters. If any part the arguments is null, the function returns null.

Examples:

Example

SELECT id, substring(id, 1, 2) country FROM orders LIMIT 3

id	country
UK2022072619373	UK
UK2022072703162	UK
US2022072676246	US

If the start argument is negative, the output depends on the value of start+length:

If start+length is greater than 1, the substring stops at position start+length - 1.
If start+length is zero, the output is empty string.
If start+length is less than zero, the output is null.

Example

SELECT substring('Lorem ipsum dolor sit amet', -5, 9)

substring
Lor

~

string ~ regex - matches string value to regex

symbol ~ regex - matches symbol value to regex

java.util.regex

!~#

string !~ regex - checks if string value does not match regex

symbol !~ regex - checks if symbol value does not match regex

to_lowercase#

to_lowercase(string) - converts all string characters to lowercase

to_uppercase#

to_uppercase(string) - converts all string characters to uppercase