Text functions

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

concat()

Description

Concatenates multiple strings, arrays or binary values.
Usage

concat(argument1, argument2, {…​).

Empty values (NULL) are ignored.

See also Operator ||.

See example 
SELECT
    concat('\xAA'::BLOB, '\xff'::BLOB) as result_blob,
    concat('I', ' ', 'love', ' ', 'Tengri') as result_string,
    concat(['T', 'e'], ['n', 'g', 'r', 'i']) as result_array;
+-------------+---------------+---------------+
| result_blob | result_string |  result_array |
+-------------+---------------+---------------+
| \xAA\xFF    | I love Tengri | {T,e,n,g,r,i} |
+-------------+---------------+---------------+

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          |
+------------+----------------+

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     |
+--------+

strlen()

Description

Returns the number of bytes in the string.
Usage

strlen(string)
See example 
SELECT
    strlen('Tengri 💙') AS strlen;
+--------+
| strlen |
+--------+
| 11     |
+--------+

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        |
+---------------+

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])}}     |
+----------------+

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     |
+----------------+

lower()

Description

Converts a string to lower case.
Usage

lower(string)
Aliases

lcase()
See example 
SELECT
    lower('TNGRi') AS lower;
+-------+
| lower |
+-------+
| tngri |
+-------+

upper()

Description

Converts a string to uppercase.
Usage

upper(string).
Aliases

ucase()
See example 
SELECT
    upper('Tengri') AS upper;
+--------+
|  upper |
+--------+
| TENGRI |
+--------+

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} |
+-----------------+

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 |
+-------+

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 |
+----------------------------------+

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 |
+------------------------------------------------------------------+

Operator ||

Description

Concatenates multiple strings, arrays or binary values.
Usage

argument1 || argument2 || argument2 || ....

Empty values (NULL) are ignored.

See also concat().

See examples 
SELECT
    '\xAA'::BLOB || '\xff'::BLOB as result_blob,
    'I' || ' ' || 'love' || ' ' || 'Tengri' as result_string,
    ['T', 'e'] || ['n', 'g', 'r', 'i'] as result_array;
+-------------+---------------+---------------+
| result_blob | result_string |  result_array |
+-------------+---------------+---------------+
|             | I love Tengri | {T,e,n,g,r,i} |
+-------------+---------------+---------------+

Note that the values in the result_blob column are not displayed in the output (because they are of type BLOB).

Using the DESCRIBE expression, output the data types for all columns in the table created in the same way as in the previous example:

CREATE TABLE concat AS

SELECT
    '\xAA'::BLOB || '\xff'::BLOB as result_blob,
    'I' || ' ' || 'love' || ' ' || 'Tengri' as result_string,
    ['T', 'e'] || ['n', 'g', 'r', 'i'] as result_array;

DESCRIBE TABLE concat;
+---------------+-------------+------+------+---------+-------+
|  column_name  | column_type | null |  key | default | extra |
+---------------+-------------+------+------+---------+-------+
| result_blob   | BLOB        | YES  | null | null    | null  |
+---------------+-------------+------+------+---------+-------+
| result_string | VARCHAR     | YES  | null | null    | null  |
+---------------+-------------+------+------+---------+-------+
| result_array  | VARCHAR[]   | YES  | null | null    | null  |
+---------------+-------------+------+------+---------+-------+