Text functions

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

:::note VARCHAR and STRING data types

QuestDB supports two types of string data: VARCHAR and STRING. Most users should use VARCHAR as it is more efficient. See VARCHAR vs STRING for more information.

Functions described in this page work with both types.

:::

concat

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

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

:::tip

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

:::

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(value) - reads length of the given value.

Arguments:

• value is a value of one of the following types:
  • varchar - length is measured in characters (result is int)
  • string - length is measured in characters (result is int)
  • symbol - length is measured in characters (result is int)
  • binary blob - length is measured in bytes (result is long)

Return value:

Returns an integer representing the length. For character types (varchar, string, symbol), returns the number of characters. For binary, returns the number of bytes. Returns -1 if the input is null.

Examples:

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

length_bytes

length_bytes(varchar) - reads length of a varchar value in bytes.

Arguments:

• varchar is a varchar value to return byte length for.

Return value:

Returns an int representing the byte length of the varchar. This is useful for multi-byte character encodings (e.g., UTF-8) where the byte length may differ from the character length. Returns -1 if the input is null.

Examples:

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

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:

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

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:

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

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');

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:

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

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:

SELECT ltrim('   QuestDB   ') AS trimmed_string;

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   ');

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:

SELECT trim('   QuestDB   ') AS trimmed_string;

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);

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');

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(symbol, ',') as symbols
FROM trades;

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

SELECT string_agg(symbol, ',') as symbols
FROM (
  SELECT symbol
  FROM trades
  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:

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;

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

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:

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;

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:

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

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.

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

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_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');

quote_ident

Arguments:

• quote_ident(string)
  • string is the string that may need quoting to be used as a SQL identifier.

Return value:

Returns the value enclosed in quotes if needed to make it a valid SQL identifier, else the value unchanged.

Examples:

SELECT quote_ident("a b");

SELECT quote_ident("ab");