How to Use the SUBSTRING() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the SUBSTRING() function in MySQL.

Using the SUBSTRING() function in MySQL

The SUBSTRING() function in MySQL is used to extract a substring from a string. It takes three arguments: the string, the starting position of the substring, and the length of the substring.

Syntax

The basic syntax of the SUBSTRING() function is as follows:

SUBSTRING(string, start_position, length)

Where:

  • string: The string to extract the substring from.
  • start_position: The position where the extraction should begin. The first position in the string is 1.
  • length: The number of characters to extract. If this argument is omitted, the function will extract all the characters from the starting position to the end of the string.

Examples

Suppose we have the following employees table:

+----+----------+-------------+---------------------+
| id | name     | department  | hire_date           |
+----+----------+-------------+---------------------+
| 1  | John Doe | Marketing   | 2022-01-01 00:00:00 |
| 2  | Jane Doe | Accounting | 2022-01-02 00:00:00 |
| 3  | Tom Smith| Marketing   | 2022-01-03 00:00:00 |
+----+----------+-------------+---------------------+

To extract the first three characters of the name of each employee, we can use the following query:

SELECT SUBSTRING(name, 1, 3) AS initials
FROM employees;

The result will be:

+----------+
| initials |
+----------+
| Joh      |
| Jan      |
| Tom      |
+----------+

In this example, we used the SUBSTRING() function to extract the first three characters of each employee’s name, and we assigned the result to an alias called initials. The result is a table that shows the initials of each employee.

#MySQL