Text functions
This page describes the available functions to assist with performing text manipulation such as concatenation, case conversion, and string length calculation.
#
concatconcat(str, ...)
- concatenates a string from one or more input values.
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.
#
lengthlength(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
a | b |
---|---|
AARON | 5 |
AMELIE | 6 |
TOM | 3 |
null | -1 |
#
leftleft(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:
name | l |
---|---|
AARON | AAR |
AMELIE | AME |
TOM | TOM |
#
rightright(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:
name | l |
---|---|
AARON | ON |
AMELIE | IE |
TOM | OM |
#
replacereplace
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 offrom_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:
replace |
---|
Hello QuestDB |
#
lpadArguments:
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:
lpad |
---|
000QuestDB |
#
ltrimArguments:
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:
trim |
---|
QuestDB |
#
rtrimrtrim
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:
rtrim |
---|
Hello QuestDB |
#
trimArguments:
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:
trim |
---|
QuestDB |
#
split_partArguments:
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:
split_part |
---|
QuestDB |
#
starts_withArguments:
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:
starts_with |
---|
true |
#
string_aggArguments:
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:
makes |
---|
Honeywell,Honeywell,United Automation,United Automation,... |
It's common to append a LIMIT
so that, such as in this case, the values
repeat:
This will return 10 concatenated strings.
#
strpos / positionstrpos(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:
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()
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:
col | col1 | col2 |
---|---|---|
apple,pear | apple | pear |
cat,dog | cat | dog |
#
substringsubstring(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 from1
.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:
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 positionstart+length - 1
. - If
start+length
is zero, the output is empty string. - If
start+length
is less than zero, the output isnull
.
substring |
---|
Lor |
#
to_lowercase / lowerto_lowercase(string)
orlower(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:
to_lowercase |
---|
questdb |
#
to_uppercase / upperto_uppercase(string)
orupper(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:
to_uppercase |
---|
QUESTDB |