MySQL SUBSTRING Function

Back to: MySQL Tutorials for Beginners and Professionals

MySQL SUBSTRING Function with Examples

In this article, I am going to discuss MySQL SUBSTRING Function with Examples. Please read our previous article where we discussed MySQL FORMAT Function with Examples.

MySQL SUBSTRING Function

Similar to the MID function, the MySQL SUBSTRING function allows us to extracts a substring with a given length from a given string starting from a specific position. Or in simple words, we can say that the MySQL SUBSTRING function returns the specified number of characters from a particular position of a given string. The SUBSTRING function works only on string data or string columns.

Syntax:

Following are the syntaxes to use the SUBSTRING function.

Parameters

1. string: Required. The string from which the substring to be extracted.
2. start: Required. The start position. It can be a positive or a negative integer number. If it is a positive number, this function extracts from the beginning of the string. If the start position is a negative number, then the SUBSTRING function starts from the end of the string and counts backward.
3. length: Optional. It is an integer number that indicates the number of characters to be extracted. If it is not given, then the whole string will be returned from the start position i.e. the remaining entire string from the start position.

Note: The first position in the string is 1.

Example:

Extracting a substring from a given string without the length parameter.
SELECT SUBSTRING(“MySQLTutorials”, 3) AS SubString;
Output: SQLTutorials

Example:

Extracting a substring from a given string with the length parameter.
SELECT SUBSTRING(“MySQLTutorials”, 3, 3) AS SubString;
Output: SQL

Example:

Extracting a substring from a given string when the starting position is negative. In this case, the SUBSTRING function will start from the end of the string and count backward.
SELECT SUBSTRING(“MySQLTutorials”, -3) AS SubString;
Output: als

Example:

Extracting a substring from a given string when the starting position is negative and also length parameter is provided.
SELECT SUBSTRING(“MySQLTutorials”, -5, 3) AS SubString;
Output: ria

Substring Function Using FROM Clause

Example:
SELECT SUBSTRING(‘MySQLTutorials’ FROM 6);
Output: Tutorials

Example:
SELECT SUBSTRING(‘MySQLTutorials’ FROM 1 FOR 5);
Output: MySQL

Example:
SELECT SUBSTRING(‘DotNetTutorials.NET’ FROM -3 FOR 3);
Output: NET

SUBSTRING Function with Database Table:

We are going to use the following Employee table to understand how to use the MySQL SUBSTRING function with the database table column with Examples.

Please use the following SQL Script to create and populate the Employee table with the required sample data.

CREATE DATABASE Company;
USE Company;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO Employee VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO Employee VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO Employee VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO Employee VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');

Example:

Now we will extract a substring from the name column starting from the third position up to 4 characters. The following is the query.

SELECT Id, Name, SUBSTRING(Name, 3, 4) As SubName, Department, City FROM Employee;

Once you execute the above query, you will get the following output.

You can also achieve the above output by using the FROM clause as follows.

SELECT Id, Name, SUBSTRING(Name FROM 3 FOR 4) As SubName, Department, City FROM Employee;

Example:

Now we will extract a substring from the name column starting from the third position to the remaining characters. In this case, we don’t require to pass the length parameter to the SUBSTRING function as shown in the below SQL query.

SELECT Id, Name, SUBSTRING(Name, 3) As SubName, Department, City FROM Employee;

Once you execute the above query, you will get the following output.

You can also achieve the above output by using the FROM clause as follows.

SELECT Id, Name, SUBSTRING(Name FROM 3) As SubName, Department, City FROM Employee;

In the next article, I am going to discuss MySQL SUBSTR Function with Examples. Here, in this article, I try to explain the MySQL SUBSTRING Function with Examples. I hope this MySQL SUBSTRING Function article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.