How to Truncate Strings in MySQL (With Example)


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;

Output:

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

The name of the truncated string column is now short_team, which is much easier to read than SUBSTRING(team, 1, 3) from the previous example.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Use DELETE with INNER JOIN
MySQL: How to Delete Rows from Table Based on id
MySQL: How to Delete Duplicate Rows But Keep Latest

Leave a Reply

Your email address will not be published. Required fields are marked *