MySQL: How to Select First N Characters of String


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.

We could also use the SUBSTRING statement to select only the first four letters of each string:

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:

MySQL: How to Check for Null in CASE Statement
MySQL: How to Use Case-Sensitive LIKE Search
MySQL: How to Use Case Statement with Multiple Conditions

Leave a Reply

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