Text functions

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

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

replace#

replace replaces all occurrences of a substring within a string with another substring.

Arguments:

  • replace(string, from_string, to_string)

    • string is the original string where replacements will be made.
    • from_string is the substring that will be searched for in the original string.
    • to_string is the substring that will replace occurrences of from_string.

Return value:

Returns a new string that is derived from the original string by replacing every occurrence of from_string with to_string.

Examples:

SELECT replace('Hello World', 'World', 'QuestDB');
replace
Hello QuestDB

lpad#

Arguments:

  • lpad(string, length, fill)

    • string is the input string that you want to pad.
    • length is the length of the resulting string after padding. If this is less than the length of the original string, the original string is truncated to the specified length.
    • fill is the string to use for padding. If this is not specified, spaces are used.

Return value:

Returns a string that is padded on the left with the specified fill string to the specified length.

Example:

Using lpad function
SELECT lpad('QuestDB', 10, '0') AS padded_string;
lpad
000QuestDB

ltrim#

Arguments:

  • ltrim(string)

    • string is the input string from which you want to remove leading whitespace.

Return value:

Returns a string with leading whitespace removed.

Example:

Using ltrim function
SELECT ltrim(' QuestDB ') AS trimmed_string;
trim
QuestDB    

rtrim#

rtrim extracts white space from the right of a string value.

Arguments:

  • rtrim(string)

Return value:

Returns a new string derived from the original string, minus all trailing occurrences of white space.

Examples:

SELECT rtrim('Hello QuestDB ');
rtrim
Hello QuestDB

trim#

Arguments:

  • trim(string)

    • string is the input string from which you want to remove leading and trailing whitespace.

Return value:

Returns a string with leading and trailing whitespace removed.

Example:

Using trim function
SELECT trim(' QuestDB ') AS trimmed_string;
trim
QuestDB

split_part#

Arguments:

  • split_part(string, delimiter, part)

    • string is the original string that will be split.
    • delimiter is the character or characters that will be used to split the string.
    • part is an integer that specifies which part to return, starting from 1.

Return value:

Returns the part at the specified position from the string that has been split by the delimiter.

Examples:

SELECT split_part('Hello,QuestDB,SQL', ',', 2);
split_part
QuestDB

starts_with#

Arguments:

  • starts_with(string, substring)

    • string is the original string that will be checked.
    • substring is the substring that will be checked if it's at the start of the original string.

Return value:

Returns true if the original string starts with the specified substring, false otherwise.

Examples:

SELECT starts_with('Hello QuestDB', 'Hello');
starts_with
true

string_agg#

Arguments:

  • string_agg(expression, delimiter)

    • expression is the string that will be aggregated.
    • delimiter is the string that will be inserted between the values in the result string.

Return value:

Returns a string that is the result of concatenating all the string values in a group, separated by the specified delimiter.

Examples:

SELECT string_agg(make, ',') as makes
FROM sensors;
makes
Honeywell,Honeywell,United Automation,United Automation,...

It's common to append a LIMIT so that, such as in this case, the values repeat:

SELECT string_agg(make, ',') as makes
FROM (
SELECT make
FROM sensors
LIMIT 10
) sub;

This will return 10 concatenated strings.

strpos / position#

strpos(string, substring) or position(string, substring) - searches for the first substring occurrence in a string, and returns the index position of the starting character. 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;
-- This is equal to:
SELECT name, position(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() or position() 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;
-- This is equal to:
SELECT col,
left(col, position(col, ',') - 1) as col1,
right(col, length(col) - position(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

to_lowercase / lower#

  • to_lowercase(string) or lower(string) - converts all upper case string characters to lowercase

Arguments:

string is the input strong to be converted.

Return value:

Return value type is string.

Examples:

SELECT lower('questDB');
-- This is equal to:
SELECT to_lowercase('questDB');
to_lowercase
questdb

to_uppercase / upper#

  • to_uppercase(string) or upper(string) - converts all lower case string characters to uppercase

Arguments:

string is the input strong to be converted.

Return value:

Return value type is string.

Examples:

SELECT upper('questDB');
-- This is equal to:
SELECT to_uppercase('questDB');
to_uppercase
QUESTDB

Something missing? Page not helpful? Please suggest an edit on GitHub.