Substring in MySQL

In this tutorial, we aim at exploring how to fetch the substring of a column in MySQL.

It becomes necessary for database developers to send data reports in shorter formats because of storage limits. Substrings of the actual string are also required while trying to mine data or fetching only the relevant information from lengthy strings.

MySQL provides us with the SUBSTR() function that takes care of this operation. The SUBSTR() function takes in three arguments: the string, the position, and the length.

The string represents the long string that needs to be altered. The position indicates where the string needs to be altered, and the length indicates the total number of characters needed in our output string.

Let us understand how this method works. But before we begin, we must create a dummy dataset by creating a student_details table with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table with rows with students’ first names and last names. To view the entries in the data, we use the following code.

SELECT * FROM student_details;

Output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Let us try to truncate the last names of the students. Assuming we need to shorten the last name to produce our students’ email addresses, the last names can contain only the first three characters.

SUBSTR Function in MySQL

The basic syntax of the SUBSTR() function is as follows.

SUBSTR(string, position, length)

Let us try to truncate our column stu_lastName from the student_details table. It can be done by using the query below.

select SUBSTR(stu_lastName, 1, 3) as shortened_lastNames
from student_details

The code above gives use the shortened last names of the students.

Output:

shortened_lastNames
San
Joh
Bro
Jos
Sha
Par
Mil

Therefore, with the help of the SUBSTR() function, we can efficiently split a string and manipulate the components of a string to generate alternatives in MySQL.