Cloud

text

The text data type is a UTF8-encoded text with Unicode support, which stores a sequence of characters (text).

Examples

Create an employee table with a text data type in each column:

CREATE TABLE employee (
    employeeName text,
    employeeDept text,
    employeeRole text
);
INSERT INTO employee (employeeName, employeeDept, employeeRole)
VALUES ('John','Finance','Staff'),
       ('Maya','Product','Staff'),
       ('Jane','Finance','Staff'),
       ('Phil','HR','Manager');

Insert the text value between the single quotes ' '.

The following output shows the created table:

+---------------+---------------+---------------+
| employeename  | employeedept  | employeerole  |
+---------------+---------------+---------------+
| John          | Finance       | Staff         |
| Maya          | Product       | Staff         |
| Jane          | Finance       | Staff         |
| Phil          | HR            | Manager       |
+---------------+---------------+---------------+

Text with SUBSTR function

The SUBSTR function extracts a specific number of characters from a text.

Syntax

substr( text, start_position, length )

The syntax includes the following parameters:

  • text is the specified text.

  • start_position is the starting position, specifying the part from which the substring is returned. Use an integer value.

  • length determines the number of characters to extract. Use one or more characters.

The first position in the text is 1.

Example

Insert a value into the text column.

SELECT substr('Watermelon',6,5) AS "Fruit";

The following output shows the result:

+-------------+
| Fruit       |
+-------------+
| melon       |
+-------------+

Text with LENGTH function

The length() function returns the number of characters in a text.

The number of characters might be different from the byte length.

Syntax

The length function will take a text as a parameter.

LENGTH (text);

Example

Insert a value into the text column.

SELECT LENGTH ('UNITED STATES');

The following output shows the result.

+---------+
| f       |
+---------+
| 13      |
+---------+

The length() function will also count spaces.