Text Type
Overview
The text data type is a UTF8-encoded text with Unicode support, which stores a sequence of characters (text).
Examples
Let’s 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');
The created table is shown below:
+---------------+---------------+---------------+
| 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 )
Let’s analyze the above syntax:
-
textis the specified text. -
start_positionis used as the starting position, specifying the part from which the substring will be returned. It is written as an int value. -
lengthis used to determine the number of characters to be extracted. It can be one or more characters.
text is 1.Example
Insert a value into the text column.
SELECT substr('Watermelon',6,5) AS "Fruit";
The updated table is shown below:
+-------------+
| Fruit |
+-------------+
| melon |
+-------------+
Text With LENGTH Function
The length() function returns the number of characters in a text.
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 updated table is shown below.
+---------+
| f |
+---------+
| 13 |
+---------+
length() function will also count spaces.