Text functions

Text functions are functions for working with text strings (data of type VARCHAR).

`array_extract()`

Description	Extracts a character from the specified string or an item from the specified list.
Usage	`array_extract(list, index)`

Extracts a character from the specified string or an element from the specified list at the specified index. Negative values are allowed for index.

The numbering of elements starts with 1.

See example

SELECT
    array_extract('Tengri', 1)  AS result_1,
    array_extract('Tengri', 3)  AS result_2,
    array_extract('Tengri', -1) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| T        | n        | i        |
+----------+----------+----------+

SELECT
    array_extract([1,2,3,4], 1)  AS result_1,
    array_extract([1,2,3,4], 3)  AS result_2,
    array_extract([1,2,3,4], -1) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 3        | 4        |
+----------+----------+----------+

`array_slice()`

Description	Extracts a substring from the specified string or a list from the specified list.
Usage	`array_slice(list, begin, end)`
Aliases	`list_slice()`

Extracts a substring from the specified string or a list from the specified list at the given start and end coordinates. Negative values are allowed for the coordinates.

Numbering of elements starts with 1.

See example

SELECT
    array_slice('Tengri', 3, 4)  AS result_1,
    array_slice('Tengri', 0, 1)  AS result_2,
    array_slice('Tengri', 0, -2) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| ng       | T        | Tengr    |
+----------+----------+----------+

SELECT
    array_slice([1,2,3,4], 3, 4)  AS result_1,
    array_slice([1,2,3,4], 0, 1)  AS result_2,
    array_slice([1,2,3,4], 0, -2) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| {3,4}    | {1}      | {1,2,3}  |
+----------+----------+----------+

`bar()`

Description	draws a bar at the height of the string.
Usage	`bar(x, min, max[, width])`

Draws a black bar at the height of the string. The width of the bar is proportional to x - min and equal to width when x = max.

The default is width = 80.

It is convenient to use for simulation of scale display in text form.

See example

SELECT
    t.generate_series AS value,
    bar(t.generate_series, 0, 5, 10) AS progress_bar
FROM generate_series(0, 5) AS t;

+-------+--------------+
| value | progress_bar |
+-------+--------------+
| 0     |              |
+-------+--------------+
| 1     | ██           |
+-------+--------------+
| 2     | ████         |
+-------+--------------+
| 3     | ██████       |
+-------+--------------+
| 4     | ████████     |
+-------+--------------+
| 5     | ██████████   |
+-------+--------------+

`bin()`

Description	Converts each character of a string to its binary representation.
Usage	`bin(string)`

See example

SELECT
    bin('T') AS result_1,
    bin('t') AS result_2,
    bin(' ') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 01010100 | 01110100 | 00100000 |
+----------+----------+----------+

`chr()`

Description	Returns the character corresponding to the value of the code ASCII or code Unicode, given in `argument`.
Usage	`chr(argument)`

See example

SELECT
    chr(84) || chr(78) || chr(71) || chr(82) || chr(105) AS chr;

+-------+
|  chr  |
+-------+
| TNGRi |
+-------+

`concat()`

Description	Concatenates multiple strings, arrays or binary values.
Usage	`concat(argument1, argument2, {…)`.

Empty values (NULL) are ignored.

`concat_ws()`

Description	Concatenates strings over a separator.
Usage	`concat_ws(separator, string1, string2, ...)`.

Concatenates multiple strings over the given separator.

If a NULL value is passed as a string, it is ignored. If a single string is passed, the same string is returned without adding a delimiter.

See example

SELECT
    concat_ws(' ', 'I', 'love', 'Tengri')       AS result_1,
    concat_ws(' ', 'I', NULL, 'love', 'Tengri') AS result_2,
    concat_ws('!', 'Tengri')                    AS result_3;

+---------------+---------------+----------+
| result_1      | result_2      | result_3 |
+---------------+---------------+----------+
| I love Tengri | I love Tengri | Tengri   |
+---------------+---------------+----------+

`contains()`

Description	Returns `true` if the specified string `string` contains the searched substring `search_string`.
Usage	`contains(string, search_string)`

See example

SELECT
    contains('I love Tengri', 'Tengri') AS check_name,
    contains('I love Tengri', 'TNGRi')  AS check_nickname;

+------------+----------------+
| check_name | check_nickname |
+------------+----------------+
| true       | false          |
+------------+----------------+

`ends_with()`

Description	Returns `true` if the string ends with the searched string.
Usage	`ends_with(string, search_string)`
Aliases	`suffix`

See example

SELECT
    ends_with('I love Tengri', 'I')      AS result_1,
    ends_with('I love Tengri', 'Tengri') AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| false    | true     |
+----------+----------+

`format()`

Description	Formats a string using the fmt syntax.
Usage	`format(string, ...)`

Formats a string using the fmt syntax.

See example

SELECT
    format('I love {}', 'Tengri')         AS result_1,
    format('{0} love {1}', 'I','Tengri' ) AS result_2,
    format('Today is {}', today())        AS result_3;

+---------------+---------------+---------------------+
| result_1      | result_2      | result_3            |
+---------------+---------------+---------------------+
| I love Tengri | I love Tengri | Today is 2026-05-06 |
+---------------+---------------+---------------------+

`greatest()`

Description	Returns the largest of the specified arguments.
Usage	`greatest(argument[, argument, ...])`

When used with numbers, returns the largest number.
When used with strings — returns the last of the strings in alphabetical order. In this case, upper case precedes lower case.
When used with types of date and time — returns the most recent object.

See examples

SELECT
    greatest(1, 2, 3, 4, 4) AS result_1,
    greatest(1, -1)         AS result_2,
    greatest(0)             AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | 1        | 0        |
+----------+----------+----------+

SELECT
    greatest('a', 'z', 'A', 'Z') AS result_1,
    greatest('Tengri', 'TNGRi')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| z        | Tengri   |
+----------+----------+

SELECT
    greatest(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    greatest(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    greatest(DATE '2024-02-29', now())                           AS result_3;

+---------------------+------------+----------------------------------+
| result_1            | result_2   | result_3                         |
+---------------------+------------+----------------------------------+
| 2024-02-29T00:00:01 | 2029-02-24 | 2026-05-06T13:05:46.173330+03:00 |
+---------------------+------------+----------------------------------+

`least()`

Description	Returns the smallest of the specified arguments.
Usage	`least(argument[, argument, ...])`

When used with numbers, returns the smallest number.
When used with strings — returns the first of the strings in alphabetical order. In this case, upper case precedes lower case.
When used with types date and time — returns the earliest object.

See examples

SELECT
    least(1, 1, 2, 3, 4) AS result_1,
    least(1, -1)         AS result_2,
    least(0)             AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | -1       | 0        |
+----------+----------+----------+

SELECT
    least(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    least(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    least(DATE '2024-02-29', now())                           AS result_3;

+---------------------+------------+---------------------------+
| result_1            | result_2   | result_3                  |
+---------------------+------------+---------------------------+
| 2024-02-29T00:00:00 | 2024-02-29 | 2024-02-29T00:00:00+03:00 |
+---------------------+------------+---------------------------+

SELECT
    least('a', 'z', 'A', 'Z') AS result_1,
    least('Tengri', 'TNGRi')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| A        | TNGRi    |
+----------+----------+

`left()`

Description	Extracts a substring of the given length from the string, starting from the left edge.
Usage	`left(string, length)`

See example

SELECT
    left('I love Tengri', 6)   AS result_1,
    left('I love Tengri', 100) AS result_2;

+----------+---------------+
| result_1 | result_2      |
+----------+---------------+
| I love   | I love Tengri |
+----------+---------------+

`length()`

Description	Returns the number of characters in a string.
Usage	`length(string)`
Aliases	`char_length()`, `character_length()`

See example

SELECT
    length('I love Tengri 💙') AS length;

+--------+
| length |
+--------+
| 15     |
+--------+

`lower()`

Description	Converts a string to lower case.
Usage	`lower(string)`
Aliases	`lcase()`

See example

SELECT
    lower('TNGRi') AS lower;

+-------+
| lower |
+-------+
| tngri |
+-------+

`ltrim()`

Description	Removes all occurrences of any of the specified characters at the beginning of a string.
Usage	`ltrim(string[, characters])`

If no characters to be deleted are specified, the default character is a space.

See examples

SELECT
    '"' || ltrim(' Tengri ') || '"' AS ltrim;

+-----------+
|   ltrim   |
+-----------+
| "Tengri " |
+-----------+

SELECT
    ltrim('{{([Tengri])}}', '{([])}') AS ltrim_brackets;

+----------------+
| ltrim_brackets |
+----------------+
| Tengri])}}     |
+----------------+

`md5()`

Description	Returns a hash MD5 of data from `argument` as a string (`VARCHAR`).
Usage	`md5(argument)`

The argument can be binary data or a string.

See examples

SELECT
    md5('\xAA\xFF'::BLOB) as md5_hash;

+----------------------------------+
|             md5_hash             |
+----------------------------------+
| 1fab7f7621f5ddc051ebd1f2c63c4665 |
+----------------------------------+

SELECT
    md5('Tengri') as md5_hash;

+----------------------------------+
|             md5_hash             |
+----------------------------------+
| 846b02d31131a10bd6ac0ba189c65bef |
+----------------------------------+

`parse_filename()`

Description	Returns the name of the file at the specified path.
Usage	`parse_filename(string[, trim_extension][, separator])`

Returns the name of the file at the specified path — the last element of the path.

If trim_extension is true, the file extension is removed (default — false).

Separator options:

system — system
both_slash — any slash (default)
forward_slash — forward slash
backslash — backslash

See example

SELECT
    parse_filename('path/to/file.csv')                        AS result_1,
    parse_filename('path/to/file.csv', true, 'forward_slash') AS result_2,
    parse_filename('path\to\file.csv')                        AS result_3,
    parse_filename('path/to/folder')                          AS result_4;

+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| file.csv | file     | file.csv | folder   |
+----------+----------+----------+----------+

`parse_path()`

Description	Returns a list of elements of the specified path.
Usage	`parse_path(string[, separator])`

Returns a list of elements (directories and filename) in the specified path.

Separator options:

system — system
both_slash — any slash (default)
forward_slash — forward slash
backslash — backslash

See example

SELECT
    parse_path('path/to/file.csv')                  AS result_1,
    parse_path('path/to/file.csv', 'forward_slash') AS result_2,
    parse_path('path\to\file.csv')                  AS result_3,
    parse_path('path/to/folder')                    AS result_4;

+--------------------+--------------------+--------------------+------------------+
| result_1           | result_2           | result_3           | result_4         |
+--------------------+--------------------+--------------------+------------------+
| {path,to,file.csv} | {path,to,file.csv} | {path,to,file.csv} | {path,to,folder} |
+--------------------+--------------------+--------------------+------------------+

`position()`

Description	Returns the position of the searched string in the string.
Usage	`position('search_string' IN 'string')`

Returns the position of the first occurrence of the searched string in the string, starting at 1. Returns 0 if no occurrences are found.

See example

SELECT
    position('Tengri' IN 'I love Tengri') AS result_1,
    position('like' IN 'I love Tengri')   AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| 8        | 0        |
+----------+----------+

`replace()`

Description	Replaces all substrings with the specified string.
Usage	`replace(string, source, target)`

Replaces all source substrings in a string string with target.

See example

SELECT
    replace('Tengri', 'engr', 'NGR')       AS result_1,
    replace('my name is Tengri', 'n', 'N') AS result_2;

+----------+-------------------+
| result_1 | result_2          |
+----------+-------------------+
| TNGRi    | my Name is TeNgri |
+----------+-------------------+

`right()`

Description	Extracts a substring of the given length from a string, starting at the right edge.
Usage	`right(string, length)`

See example

SELECT
    right('I love Tengri', 6)   AS result_1,
    right('I love Tengri', 100) AS result_2;

+----------+---------------+
| result_1 | result_2      |
+----------+---------------+
| Tengri   | I love Tengri |
+----------+---------------+

`rtrim()`

Description	Removes all occurrences of any of the specified characters at the end of a string.
Usage	`rtrim(string[, characters])`

If no characters to be deleted are specified, the default character is a space.

See examples

SELECT
    '"' || rtrim(' Tengri ') || '"' AS ltrim;

+-----------+
|   ltrim   |
+-----------+
| " Tengri" |
+-----------+

SELECT
    rtrim('{{([Tengri])}}', '{([])}') AS rtrim_brackets;

+----------------+
| rtrim_brackets |
+----------------+
| {{([Tengri     |
+----------------+

`sha1()`

Description	Returns a hash SHA-1 of the data from `argument` as a string (`VARCHAR`).
Usage	`sha1(argument)`

The argument can be binary data or a string.

See examples

SELECT
    sha1('\xAA\xFF'::BLOB) as sha1_hash;

+------------------------------------------+
|                 sha1_hash                |
+------------------------------------------+
| e89b0db325637edfacde04a76005c492e2c5aeca |
+------------------------------------------+

SELECT
    sha1('Tengri') as sha1_hash;

+------------------------------------------+
|                 sha1_hash                |
+------------------------------------------+
| b514525a19995a2442d7565bfd9bb42d9dc71a13 |
+------------------------------------------+

`sha256()`

Description	Returns a hash SHA-256 of the data from `argument` as a string (`VARCHAR`).
Usage	`sha256(argument)`

The argument can be binary data or a string.

See example

SELECT
    sha256('\xAA\xFF'::BLOB) as sha256_hash;

+------------------------------------------------------------------+
|                            sha256_hash                           |
+------------------------------------------------------------------+
| 768318522cac43261e8ef4946c2296a3643d523a8d5bda8ff5b82aa64470421a |
+------------------------------------------------------------------+

SELECT
    sha256('Tengri') as sha256_hash;

+------------------------------------------------------------------+
|                            sha256_hash                           |
+------------------------------------------------------------------+
| 8aaacef66663b14ee7c5a03dbaec7b40f0f3bf17bd12d2ed4f9aaad0e10a0d77 |
+------------------------------------------------------------------+

`split()`

Description	Splits a string into two parts by the given separator.
Usage	`split(string, separator)`
Aliases	`str_split`, `string_split`, `string_to_array`

See example

SELECT
    split('I love Tengri', ' ') AS words;

+-----------------+
|      words      |
+-----------------+
| {I,love,Tengri} |
+-----------------+

`starts_with()`

Description	Returns `true` if the string starts with the searched string.
Usage	`starts_with(string, search_string)`

See example

SELECT
    starts_with('I love Tengri', 'I')      AS result_1,
    starts_with('I love Tengri', 'Tengri') AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| true     | false    |
+----------+----------+

`strlen()`

Description	Returns the number of bytes in the string.
Usage	`strlen(string)`

See example

SELECT
    strlen('Tengri 💙') AS strlen;

+--------+
| strlen |
+--------+
| 11     |
+--------+

`substring()`

Description	Extracts a substring from a string.
Usage	`substring(string, start[, length])`
Aliases	`substr`

Extracts a substring from the specified string string, starting at the start character and ending at the end of the string. If the optional length argument is given, a substring of length characters is extracted. Note that the numbering of characters in the source string starts with 1.

See example

SELECT
    substring('I love Tengri', 1)       AS substring_1,
    substring('I love Tengri', 3)       AS substring_2,
    substring('I love Tengri', 3, 4)    AS substring_3;

+---------------+-------------+-------------+
| substring_1   | substring_2 | substring_3 |
+---------------+-------------+-------------+
| I love Tengri | love Tengri | love        |
+---------------+-------------+-------------+

`to_base64()`

Description	Encodes binary data into a Base64 text string.
Usage	`to_base64(argument)`
Aliases	`base64`

See example

SELECT
    to_base64('A'::BLOB) AS result_1,
    to_base64('B'::BLOB) AS result_2,
    to_base64('C'::BLOB) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| QQ==     | Qg==     | Qw==     |
+----------+----------+----------+

`trim()`

Description	Removes all occurrences of any of the specified characters on both sides of the string.
Usage	`trim(string[, characters])`

If no characters to be deleted are specified, the default character is a space.

See examples

SELECT
    '"' || trim('  Tengri ') || '"' AS trim;

+----------+
|   trim   |
+----------+
| "Tengri" |
+----------+

SELECT
    trim('[Tengri]', '{([])}') AS trim_brackets;

+---------------+
| trim_brackets |
+---------------+
| Tengri        |
+---------------+

`unicode()`

Description	Returns the code Unicode of the first character of a string as an integer.
Usage	`unicode(string)`
Aliases	`ascii`, `ord`

See example

SELECT
    unicode('Tengri') AS result_1,
    unicode('Тенгри') AS result_2,
    unicode(' ')      AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 84       | 1058     | 32       |
+----------+----------+----------+

`upper()`

Description	Converts a string to uppercase.
Usage	`upper(string)`
Aliases	`ucase()`

See example

SELECT
    upper('Tengri') AS upper;

+--------+
|  upper |
+--------+
| TENGRI |
+--------+

Operator `||`

Description	Concatenates multiple strings, arrays or binary values.
Usage	`argument1 \|\| argument2 \|\| argument2 \|\| ...`.

Empty values (NULL) are ignored.

Text functions

array_extract()

array_slice()

bar()

bin()

chr()

concat()

concat_ws()

contains()

ends_with()

format()

greatest()

least()

left()

length()

lower()

ltrim()

md5()

parse_filename()

parse_path()

position()

replace()

right()

rtrim()

sha1()

sha256()

split()

starts_with()

strlen()

substring()

to_base64()

trim()

unicode()

upper()

Operator ||

`array_extract()`

`array_slice()`

`bar()`

`bin()`

`chr()`

`concat()`

`concat_ws()`

`contains()`

`ends_with()`

`format()`

`greatest()`

`least()`

`left()`

`length()`

`lower()`

`ltrim()`

`md5()`

`parse_filename()`

`parse_path()`

`position()`

`replace()`

`right()`

`rtrim()`

`sha1()`

`sha256()`

`split()`

`starts_with()`

`strlen()`

`substring()`

`to_base64()`

`trim()`

`unicode()`

`upper()`

Operator `||`