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;
firstNamelastNameconcat
TimThompsonTim Thompson
AnnaThompsonAnna Thompson
AnnaMasonAnna Mason
TomJohnsonTom Johnson
TimSmithTim 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
ab
AARON5
AMELIE6
TOM3
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
namel
AARONAAR
AMELIEAME
TOMTOM

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
namel
AARONON
AMELIEIE
TOMOM

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
nameidx
Tim Thompson5
Anna Thompson6
Anna Mason0
Tom Johnson0

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;
colcol1col2
apple,pearapplepear
cat,dogcatdog

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
idcountry
UK2022072619373UK
UK2022072703162UK
US2022072676246US

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