What is the Process of Truncating Strings in MySQL?

---

You can use the following syntax in MySQL to truncate the string returned from a column in MySQL:

SELECT id, SUBSTRING(team, 1, 3), points FROM athletes;

This particular example selects the id column, the characters in positions 1 through 3 of the team column, and the points column from the table named athletes.

By using the SUBSTRING statement, we are able to truncate the text returned from the team column.

The following example shows how to use this syntax in practice.

Example: How to Truncate Strings in MySQL

Suppose we have the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  id INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL,
  assists INT NOT NULL,
  rebounds INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Kings', 14, 5, 13);
INSERT INTO athletes VALUES (0003, 'Lakers', 37, 6, 10);
INSERT INTO athletes VALUES (0004, 'Nets', 19, 10, 3);
INSERT INTO athletes VALUES (0005, 'Knicks', 26, 12, 8);
INSERT INTO athletes VALUES (0006, 'Celtics', 15, 1, 2);

-- view all rows in table
SELECT * FROM athletes;

Output:

+----+---------+--------+---------+----------+
| id | team    | points | assists | rebounds |
+----+---------+--------+---------+----------+
|  1 | Mavs    |     22 |       4 |        3 |
|  2 | Kings   |     14 |       5 |       13 |
|  3 | Lakers  |     37 |       6 |       10 |
|  4 | Nets    |     19 |      10 |        3 |
|  5 | Knicks  |     26 |      12 |        8 |
|  6 | Celtics |     15 |       1 |        2 |
+----+---------+--------+---------+----------+

We can use the following syntax to select the id, team and points columns while truncating the text from the team column to only show the first three characters of each team name:

SELECT id, SUBSTRING(team, 1, 3), points FROM athletes;

Output:

+----+-----------------------+--------+
| id | SUBSTRING(team, 1, 3) | points |
+----+-----------------------+--------+
|  1 | Mav                   |     22 |
|  2 | Kin                   |     14 |
|  3 | Lak                   |     37 |
|  4 | Net                   |     19 |
|  5 | Kni                   |     26 |
|  6 | Cel                   |     15 |
+----+-----------------------+--------+

Notice that only the first three characters from each team name in the team column are shown in the output.

If you’d like, you can also use AS to assign an alias to the resulting truncated string column:

SELECT id, SUBSTRING(team, 1, 3) AS short_team, points FROM athletes;

+----+------------+--------+
| id | short_team | points |
+----+------------+--------+
|  1 | Mav        |     22 |
|  2 | Kin        |     14 |
|  3 | Lak        |     37 |
|  4 | Net        |     19 |
|  5 | Kni        |     26 |
|  6 | Cel        |     15 |
+----+------------+--------+