MySQL SUBSTRING() with Examples

SUBSTRING() function in SQL language (or SUBSTR()) is used to split a string. In other words, it allows to extract a part of a string, for example to truncate a text.
 

Syntax:

SUBSTRING() function can be used in 4 different ways, as follows:

• SUBSTRING(str, start) : returns the string from “str” from the position defined by “start” (position)
• SUBSTRING(str FROM start) : same as above
• SUBSTRING(str, start, length) : returns the string “str” starting from the position defined by “start” with a length defined by “length”
• SUBSTRING(str FROM start FOR length) : same as previous

Note : don’t forget that the function is also sometimes called SUBSTR().
 

 

Example 1:

This function can be used in a SQL query using syntax like this:

SELECT SUBSTR(column_name, 3, 10) FROM table

In this example, the content of the column “column_name” will be truncated from the 4th character up to 10 characters.
 

Example 2:

Let’s suppose a database that contains a list of countries.

+------------+---------------+
| country_id |  country_name |
+------------+---------------+
|      1     |  CHINA        |
|      2     |  India        |
|      3     |  GERMANY      |
|      4     |  SPAIN        |
|      5     |  FRANCE       |
+------------+---------------+

An SQL query will be built to extract the following information:

• The country identifier
• The country name
• The first 2 characters of the country name (from the 1st character with a length of 2 characters)
• The whole name, without the first 2 characters (i.e. the whole name from the 3rd character)

 

 
To get all these data, you should use the following SQL query:

SELECT id, country_name, SUBSTR(country_name, 1, 2), SUBSTR(country_name, 3)
FROM country

This query will return the following results:

+------------+---------------+----------------------------+-------------------------+
| country_id |  country_name | SUBSTR(country_name, 1, 2) | SUBSTR(country_name, 3) |
+------------+---------------+----------------------------+-------------------------+
|      1     |  CHINA        | CH                         | INA                     |
|      2     |  INDIA        | IN                         | DIA                     |
|      3     |  GERMANY      | GE                         | RMANY                   |
|      4     |  SPAIN        | SP                         | AIN                     |
|      5     |  FRANCE       | FR                         | ANCE                    |
+------------+---------------+----------------------------+-------------------------+

This output table shows that it is possible to split a text simply.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More