How to Select the First N Characters of a String in MySQL

When working with text data in MySQL, you may sometimes need to extract only the first few characters from a string. This is useful for data cleaning, reporting, categorization, search, and text preprocessing.

For example, you may want to extract the first four characters from a team name, product name, customer code, or other text field.

MySQL provides two convenient ways to select the first N characters of a string:

Method 1: Using LEFT()

SELECTLEFT(team, 4)FROM athletes;

Method 2: Using SUBSTRING()

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

Both queries return the first four characters from each value in the team column.

Example: How to Select First N Characters of a String in MySQL

Suppose we have a table named athletes containing information about basketball players.

We can create the table using:

CREATETABLE athletes (    id INTPRIMARYKEY,    team TEXT NOTNULL,    position TEXT NOTNULL,    points INTNOTNULL);

Next, insert some sample data:

INSERTINTO athletes VALUES(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 view the table using:

SELECT*FROM athletes;

The output is:

+----+-----------+----------+--------+| 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 |+----+-----------+----------+--------+

Suppose we want to extract the first four characters from every value in the team column.

Method 1: Select the First N Characters Using LEFT()

The simplest approach is to use the MySQL LEFT() function.

The syntax is:

LEFT(string, number_of_characters)

For example:

SELECTLEFT(team, 4)FROM athletes;

The output is:

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

The 4 tells MySQL to return the first four characters of each value.

For example:

grizzlies  → Grizmavericks  → MaveCAVALIERS  → CavaSpurs      → SpurhawKs      → Hawknets       → Nets

Method 2: Select the First N Characters Using SUBSTRING()

You can also use the SUBSTRING() function to extract the first N characters.

The basic syntax is:

SUBSTRING(string, starting_position, number_of_characters)

To extract the first four characters:

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

The output is:

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

The arguments mean:

• team — the column containing the string
• 1 — start at the first character
• 4 — return four characters

Unlike some programming languages that use zero-based indexing, MySQL’s SUBSTRING() positions start at 1.

LEFT() vs SUBSTRING() in MySQL

Both functions can extract the first N characters, but they have slightly different purposes.

If you only need the first few characters, LEFT() is generally more concise:

SELECTLEFT(team, 4)FROM athletes;

If you need more flexible string extraction, SUBSTRING() can be more useful:

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

Give the Extracted Column a Name Using AS

By default, MySQL uses the function expression as the column name.

For example:

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

produces a column named:

SUBSTRING(team, 1, 4)

This isn’t particularly readable.

You can use AS to provide a meaningful alias:

SELECT SUBSTRING(team, 1, 4) AS first_fourFROM athletes;

The output becomes:

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

You can also use an alias with LEFT():

SELECTLEFT(team, 4) AS first_fourFROM athletes;

Select the First 3 Characters

To extract the first three characters, use:

SELECTLEFT(team, 3) AS first_threeFROM athletes;

The result would be:

+-------------+| first_three |+-------------+| Gri         || Mav         || Cav         || Spa         || Haw         || Net         |+-------------+

Using SUBSTRING():

SELECT SUBSTRING(team, 1, 3) AS first_threeFROM athletes;

Both approaches produce the same result.

Select the First 5 Characters

To extract the first five characters:

SELECTLEFT(team, 5) AS first_fiveFROM athletes;

Or:

SELECT SUBSTRING(team, 1, 5) AS first_fiveFROM athletes;

The result would be:

+------------+| first_five |+------------+| Grizz      || Maver      || Caval      || Spurs      || hawKs      || nets       |+------------+

What Happens When a String Has Fewer Than N Characters?

MySQL does not produce an error if you request more characters than a string contains.

For example:

SELECTLEFT('Nets', 10) ASresult;

returns:

+--------+| result |+--------+| Nets   |+--------+

Because Nets contains only four characters, MySQL returns all four available characters.

The same behavior applies to SUBSTRING():

SELECT SUBSTRING('Nets', 1, 10) ASresult;

Result:

+--------+| result |+--------+| Nets   |+--------+

Extract the First N Characters from a Literal String

You don’t have to use a table column. You can also use LEFT() with a literal string.

For example:

SELECTLEFT('DataScience', 4) ASresult;

Output:

+--------+| result |+--------+| Data   |+--------+

Using SUBSTRING():

SELECT SUBSTRING('DataScience', 1, 4) ASresult;

Output:

+--------+| result |+--------+| Data   |+--------+

Select the First N Characters After Filtering Rows

You can combine LEFT() with a WHERE clause.

For example, suppose we only want teams where the player scored more than 20 points:

SELECT    team,LEFT(team, 4) AS first_four,    pointsFROM athletesWHERE points >20;

This allows you to extract part of the string while also filtering the underlying records.

Select the First N Characters and Sort the Results

You can also use the extracted value in an ORDER BY clause.

For example:

SELECT    team,LEFT(team, 4) AS first_fourFROM athletesORDERBY first_four;

This sorts the results based on the extracted four-character value.

Select the First N Characters and Remove Case Differences

If your data contains inconsistent capitalization, you can combine LEFT() with LOWER() or UPPER().

For example:

SELECT    team,    LOWER(LEFT(team, 4)) AS first_fourFROM athletes;

This produces normalized lowercase values such as:

grizmavecavaspurhawknets

Alternatively:

SELECT    team,    UPPER(LEFT(team, 4)) AS first_fourFROM athletes;

produces:

GRIZMAVECAVASPURHAWKNETS

This can be useful when preparing inconsistent text data for analysis.

Use LEFT() with CONCAT()

You can combine LEFT() with other MySQL string functions.

For example, suppose you want to create a label containing the first four characters and the player’s points:

SELECT    CONCAT(LEFT(team, 4), '-', points) AS team_scoreFROM athletes;

A result might look like:

+------------+| team_score |+------------+| Griz-15    || Mave-22    || Cava-36    || Spur-18    || Hawk-40    || Nets-25    |+------------+

This can be useful for generating labels and display fields.

LEFT() vs SUBSTRING(): Which Should You Use?

If your goal is simply to select the first N characters, LEFT() is usually the cleaner option:

SELECTLEFT(team, 4) AS first_fourFROM athletes;

It’s short and immediately communicates that you want characters from the left side of the string.

Use SUBSTRING() when you need more control over where extraction begins.

For example, to extract four characters beginning with the second character:

SELECT SUBSTRING(team, 2, 4) AS extractedFROM athletes;

For grizzlies, this returns:

rizz

You cannot express that particular operation as simply with LEFT() because LEFT() always starts at the beginning of the string.

Practical Uses of Extracting the First N Characters

Extracting the first N characters is useful in many real-world database applications.

Product Codes

Suppose product codes contain a category prefix:

ELEC-10001ELEC-10002FURN-20001FURN-20002

You can extract the first four characters:

SELECT    product_code,LEFT(product_code, 4) AS categoryFROM products;

This produces:

ELECELECFURNFURN

Customer IDs

Suppose customer IDs follow a pattern:

US123456UK123456CA123456AU123456

You could extract the country prefix:

SELECT    customer_id,LEFT(customer_id, 2) AS country_codeFROM customers;

Financial Data

Financial systems often contain identifiers with prefixes indicating a product, region, or account type.

For example:

SELECT    account_number,LEFT(account_number, 3) AS account_prefixFROM accounts;

This can help categorize records.

Data Cleaning

When importing data from external systems, you may encounter long strings where only the first portion is relevant.

For example:

SELECTLEFT(description, 50) AS short_descriptionFROM products;

This can create a shortened description for reporting or dashboard display.

Important Note About Characters and Multibyte Text

LEFT() and SUBSTRING() operate on characters rather than simply taking a fixed number of bytes when working with MySQL character strings.

This matters when your database contains Unicode text such as accented characters or non-Latin scripts.

For example:

SELECTLEFT('Café', 3) ASresult;

returns the first three characters rather than arbitrarily cutting the underlying encoded bytes.

For multilingual applications, however, it is still important to configure your database and columns with an appropriate character set, typically utf8mb4.

Complete MySQL Example

The following complete example can be copied and run in MySQL:

CREATETABLE athletes (    id INTPRIMARYKEY,    team VARCHAR(50) NOTNULL,    position VARCHAR(50) NOTNULL,    points INTNOTNULL);INSERTINTO athletes VALUES(1, 'grizzlies', 'Guard', 15),(2, 'mavericks', 'Guard', 22),(3, 'CAVALIERS', 'Forward', 36),(4, 'Spurs', 'Guard', 18),(5, 'hawKs', 'Forward', 40),(6, 'nets', 'Forward', 25);-- Using LEFT()SELECT    team,LEFT(team, 4) AS first_fourFROM athletes;-- Using SUBSTRING()SELECT    team,    SUBSTRING(team, 1, 4) AS first_fourFROM athletes;

Both queries extract the first four characters from the team column.

Frequently Asked Questions

How do I select the first 4 characters in MySQL?

Use LEFT():

SELECTLEFT(team, 4)FROM athletes;

Or SUBSTRING():

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

How do I select the first 10 characters in MySQL?

Use:

SELECTLEFT(column_name, 10)FROM table_name;

Or:

SELECT SUBSTRING(column_name, 1, 10)FROM table_name;

What is the difference between LEFT() and SUBSTRING()?

LEFT() extracts characters from the beginning of a string, while SUBSTRING() allows you to specify both a starting position and the number of characters.

Does MySQL LEFT() count spaces?

Yes. Spaces are characters and are included when determining the number of characters returned.

Can I give the result a column name?

Yes. Use AS:

SELECTLEFT(team, 4) AS first_fourFROM athletes;

What happens if the string is shorter than N characters?

MySQL returns the characters that are available rather than producing an error.

Conclusion

MySQL provides two simple ways to extract the first N characters from a string: LEFT() and SUBSTRING().

For straightforward extraction from the beginning of a string, you can use:

SELECTLEFT(team, 4) AS first_fourFROM athletes;

Alternatively, use:

SELECT SUBSTRING(team, 1, 4) AS first_fourFROM athletes;

Both return the first four characters.

The main difference is flexibility. LEFT() is concise and ideal when you always want characters from the beginning, while SUBSTRING() is more flexible because you can specify where the extraction starts.

For most simple first-N-character operations, LEFT() is the easiest MySQL function to remember and use.