How can the first N characters of a string be selected in MySQL?

---

There are two ways to select the first n characters of a string in MySQL:

Method 1: Use LEFT

SELECT LEFT(team, 4) FROM athletes;

Method 2: Use SUBSTRING

SELECT SUBSTRING(team, 1, 4) FROM athletes; 

Both of these examples select the first 4 characters of the strings in the team column of the table named athletes.

The following example shows how to use each of these methods in practice.

Example: How to Select First N Characters of String 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,
  position TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'grizzlies', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'mavericks', 'Guard', 22);
INSERT INTO athletes VALUES (0003, 'CAVALIERS', 'Forward', 36);
INSERT INTO athletes VALUES (0004, 'Spurs', 'Guard', 18);
INSERT INTO athletes VALUES (0005, 'hawKs', 'Forward', 40);
INSERT INTO athletes VALUES (0006, 'nets', 'Forward', 25);

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

Output:

+----+-----------+----------+--------+
| id | team      | position | points |
+----+-----------+----------+--------+
|  1 | grizzlies | Guard    |     15 |
|  2 | mavericks | Guard    |     22 |
|  3 | CAVALIERS | Forward  |     36 |
|  4 | Spurs     | Guard    |     18 |
|  5 | hawKs     | Forward  |     40 |
|  6 | nets      | Forward  |     25 |
+----+-----------+----------+--------+

We can use the LEFT statement to select only the first four letters of each string in the team column:

SELECT LEFT(team, 4) FROM athletes;

Output:

+---------------+
| LEFT(team, 4) |
+---------------+
| Griz          |
| Mave          |
| Cava          |
| Spur          |
| Hawk          |
| Nets          |
+---------------+

Notice that the output contains only the first four letters of each string in the team column.

SELECT SUBSTRING(team, 1, 4) FROM athletes;

Output:

+-----------------------+
| SUBSTRING(team, 1, 4) |
+-----------------------+
| Griz                  |
| Mave                  |
| Cava                  |
| Spur                  |
| Hawk                  |
| Nets                  |
+-----------------------+

If we’d like, we can also use AS to assign an alias to the column name in the output:

SELECT SUBSTRING(team, 1, 4) AS first_four FROM athletes;

Output:

+------------+
| first_four |
+------------+
| Griz       |
| Mave       |
| Cava       |
| Spur       |
| Hawk       |
| Nets       |
+------------+

Notice that the column name in the output is now first_four, which is much easier to read.

Additional Resources

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