MySQL Limit the Number of Characters Returned

MySQL Limit the Number of Characters Returned, you may sometimes need to truncate a string and return only a specific number of characters from a column.

For example, suppose a table contains team names such as:

MavericksLakersCeltics

and you only want to display the first three characters:

MavLakCel

MySQL provides the SUBSTRING() function for this purpose.

The basic syntax is:

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

This starts at character position 1 and returns 3 characters.

You can also use AS to give the resulting column a more meaningful name:

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

This technique is useful for data cleaning, reporting, data analysis, database applications, and preparing text for dashboards or exports.

Example: MySQL Limit the Number of Characters Returned

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

-- create tableCREATETABLE athletes (    id INTPRIMARYKEY,    team TEXT NOTNULL,    points INTNOTNULL,    assists INTNOTNULL,    rebounds INTNOTNULL);

We can insert some sample data:

INSERTINTO athletes VALUES(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);

To view all of the rows, use:

SELECT*FROM athletes;

The output is:

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

Suppose we want to select the id, team, and points columns while displaying only the first three characters of each team name.

We can use:

SELECT    id,    SUBSTRING(team, 1, 3),    pointsFROM athletes;

The output is:

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

Only the first three characters of each value in the team column are returned.

Give the Truncated String an Alias

The default column name is:

SUBSTRING(team, 1, 3)

This can make query results difficult to read.

You can use the AS keyword to provide a descriptive name:

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

The result is:

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

The short_team alias makes the result much easier to understand.

How SUBSTRING() Works in MySQL

The basic syntax is:

SUBSTRING(string, start_position, length)

For example:

SELECT SUBSTRING('Celtics', 1, 3);

returns:

Cel

The arguments mean:

• string — the text you want to extract characters from.
• start_position — the position where extraction begins.
• length — the number of characters to return.

MySQL uses 1-based character positions, meaning the first character is at position 1.

For example, consider:

Celtics

The character positions are:

C e l t i c s1 2 3 4 5 6 7

Therefore:

SELECT SUBSTRING('Celtics', 1, 3);

returns:

Cel

Select the First N Characters-MySQL Limit the Number of Characters Returned

You can change the third argument to select a different number of characters.

For example, to select the first two characters:

SELECT SUBSTRING(team, 1, 2) AS short_teamFROM athletes;

Output:

+------------+| short_team |+------------+| Ma         || Ki         || La         || Ne         || Kn         || Ce         |+------------+

To select the first five characters:

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

Output:

+------------+| short_team |+------------+| Mavs       || Kings      || Lakers     || Nets       || Knicks     || Celti      |+------------+

If the requested length is greater than the actual string length, MySQL returns the characters that are available.

Use LEFT() to Select the First Characters

For selecting characters specifically from the beginning of a string, MySQL also provides the LEFT() function.

For example:

SELECTLEFT(team, 3) AS short_teamFROM athletes;

This produces:

+------------+| short_team |+------------+| Mav        || Kin        || Lak        || Net        || Kni        || Cel        |+------------+

The LEFT() syntax is:

LEFT(string, number_of_characters)

So:

LEFT(team, 3)

means “return the first three characters from team.”

SUBSTRING() vs LEFT()

Both functions can return the first N characters, but SUBSTRING() is more flexible.

If you always want characters from the beginning, LEFT() is concise:

SELECTLEFT(team, 3)FROM athletes;

If you need to specify a starting position, SUBSTRING() is more appropriate:

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

Select Characters Starting at a Specific Position

SUBSTRING() isn’t limited to extracting characters from the beginning.

For example:

SELECT SUBSTRING('Celtics', 2, 3);

returns:

elt

The extraction begins at character 2 and continues for three characters.

For Celtics:

C e l t i c s  ↑start = 2

The three returned characters are:

elt

This flexibility is one of the main advantages of SUBSTRING().

Select the Last N Characters

If you want characters from the end of a string, you can use RIGHT().

For example:

SELECTRIGHT(team, 3) AS last_threeFROM athletes;

The output is:

+------------+| last_three |+------------+| avs        || ngs        || ers        || ets        || cks        || ics        |+------------+

You can also use SUBSTRING() with a negative starting position:

SELECT SUBSTRING(team, -3) AS last_threeFROM athletes;

This also returns the last three characters.

Truncate a String to a Specific Length

Suppose you have product names and want to display no more than 10 characters.

You could use:

SELECT    product_name,LEFT(product_name, 10) AS short_nameFROM products;

For example:

+----------------------+------------+| product_name         | short_name |+----------------------+------------+| Wireless Keyboard    | Wireless K || Gaming Mouse         | Gaming Mou || Laptop Stand         | Laptop Sta |+----------------------+------------+

This can be useful when preparing compact reports or displaying text in a fixed-width interface.

Truncate Strings in a WHERE Condition

You can also use SUBSTRING() inside a WHERE clause.

For example:

SELECT*FROM athletesWHERE SUBSTRING(team, 1, 3) ='Mav';

This returns rows where the first three characters of team are Mav.

For the sample data, this returns:

+----+------+--------+---------+----------+| id | team | points | assists | rebounds |+----+------+--------+---------+----------+|  1 | Mavs |     22 |       4 |        3 |+----+------+--------+---------+----------+

For large tables, however, be aware that applying a function to a column in a filter can affect index usage and query performance.

Create a Short Code from a String

String truncation can also be useful for creating simple identifiers or abbreviated labels.

For example:

SELECT    id,    team,    UPPER(SUBSTRING(team, 1, 3)) AS team_codeFROM athletes;

Output:

+----+---------+-----------+| id | team    | team_code |+----+---------+-----------+|  1 | Mavs    | MAV       ||  2 | Kings   | KIN       ||  3 | Lakers  | LAK       ||  4 | Nets    | NET       ||  5 | Knicks  | KNI       ||  6 | Celtics | CEL       |+----+---------+-----------+

This combines SUBSTRING() with UPPER() to create three-character uppercase labels.

Truncate and Add an Ellipsis

A common requirement in reports and applications is to show a shortened version of a long string followed by ....

For example:

SELECTCASEWHEN CHAR_LENGTH(team) >5THEN CONCAT(LEFT(team, 5), '...')ELSE teamENDAS short_teamFROM athletes;

This is useful when you want to shorten longer strings without unnecessarily adding an ellipsis to short values.

For example:

Celtics

could become:

Celti...

while:

Mavs

remains:

Mavs

SUBSTRING() and NULL Values

If the source column contains NULL, SUBSTRING() returns NULL.

For example:

SELECT SUBSTRING(NULL, 1, 3);

returns:

NULL

If you want to replace NULL with another value, you can use COALESCE():

SELECT    COALESCE(SUBSTRING(team, 1, 3), 'N/A') AS short_teamFROM athletes;

This returns N/A when team is NULL.

SUBSTRING() vs SUBSTR()

MySQL also supports SUBSTR() as a synonym for SUBSTRING().

These queries are equivalent:

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

and:

SELECT SUBSTR(team, 1, 3)FROM athletes;

For readability, SUBSTRING() is often preferable in tutorials and shared SQL code because its purpose is immediately apparent.

Complete MySQL Example

The following example can be copied and executed directly in MySQL:

CREATETABLE athletes (    id INTPRIMARYKEY,    team VARCHAR(50) NOTNULL,    points INTNOTNULL,    assists INTNOTNULL,    rebounds INTNOTNULL);INSERTINTO athletes VALUES(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);SELECT    id,    SUBSTRING(team, 1, 3) AS short_team,    pointsFROM athletes;

The final query returns:

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

Common Mistakes When Truncating Strings in MySQL

Starting at Position 0

A common mistake is assuming that MySQL uses zero-based string positions.

For example:

SUBSTRING(team, 0, 3)

is not the normal way to request the first three characters.

Use:

SUBSTRING(team, 1, 3)

MySQL’s regular positive starting positions begin at 1.

Confusing Length with Ending Position

In:

SUBSTRING(team, 2, 3)

the 3 means three characters, not “stop at character 3.”

It starts at position 2 and returns three characters.

For example:

Celtics

becomes:

elt

Forgetting an Alias

This:

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

works, but the result column is named:

SUBSTRING(team, 1, 3)

For reporting, this is generally clearer:

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

Frequently Asked Questions

How do I truncate a string in MySQL?

Use SUBSTRING():

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

This returns the first three characters.

How do I select the first N characters in MySQL?

Use either LEFT() or SUBSTRING():

SELECTLEFT(team, 5)FROM athletes;

or:

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

How do I select the last N characters?

Use RIGHT():

SELECTRIGHT(team, 3)FROM athletes;

Or use SUBSTRING() with a negative starting position:

SELECT SUBSTRING(team, -3)FROM athletes;

Does SUBSTRING() modify the original data?

No. A SELECT query using SUBSTRING() only changes the value returned in the result set. It does not modify the original column.

How do I give the truncated string a name?

Use AS:

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

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

LEFT() is designed to return characters from the beginning of a string:

LEFT(team, 3)

SUBSTRING() lets you specify both the starting position and number of characters:

SUBSTRING(team, 2, 3)

Conclusion

MySQL provides several useful functions for truncating and extracting portions of strings. The SUBSTRING() function is particularly flexible because you can specify exactly where the extraction starts and how many characters should be returned.

The basic syntax is:

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

For example, the value:

Celtics

becomes:

Cel

You can also use LEFT() when you simply need the first N characters and RIGHT() when you need the last N characters.

These functions are useful for SQL data cleaning, reporting, text transformation, ETL pipelines, analytics, dashboard development, and database applications where displaying or processing only part of a text value is required