MySQL SUBSTR Function

Back to: MySQL Tutorials for Beginners and Professionals

MySQL SUBSTR Function with Examples

In this article, I am going to discuss MySQL SUBSTR Function with Examples. Please read our previous article where we discussed MySQL SUBSTRING Function with Examples. The MySQL SUBSTR function is a synonym of a SUBSTRING function that returns a substring.

MySQL SUBSTRING Function

Similar to MID and SUBSTRING functions, The MySQL SUBSTR function allows us to extracts a substring from a given string starting from a specific position. The SUBSTR function works only on string data or string columns.

Syntax:

Following are the syntaxes to use the SUBSTR 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 the sub-string from the beginning of the string. If the start position is a negative number, then the SUBSTR function starts from the end of the string.
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.

Note: The first position in the string is 1.

Example:

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

Example:

Extracting a substring from a given string with the length parameter.
SELECT SUBSTR(“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 SUBSTR function will start from the end of the string and count backward.
SELECT SUBSTR(“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 SUBSTR(“MySQLTutorials”, -5, 3) AS SubString;
Output: ria

SUBSTR Function Using FROM clause

Example:
SELECT SUBSTR(‘MySQLTutorials’ FROM 6) AS SubString;
Output: Tutorials

Example:
SELECT SUBSTR(‘MySQLTutorials’ FROM 1 FOR 5) AS SubString;
Output: MySQL

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

MySQL SUBSTR Function with Database Table:

We are going to use the following Employee table to understand how to use the MySQL SUBSTR 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 the sub-string from the name column starting from the third position up to 5 characters. The following is the query.

SELECT Id, Name, SUBSTR(Name, 3, 5) 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, SUBSTR(Name FROM 3 FOR 4) As SubName, Department, City FROM Employee;

Example:

Now we will extract the sub-string 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 SUBSTR function as shown in the below SQL query.

SELECT Id, Name, SUBSTR(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, SUBSTR(Name FROM 3) As SubName, Department, City FROM Employee;

In the next article, I am going to discuss MySQL COALESCE Function with Examples. Here, in this article, I try to explain the MySQL SUBSTR Function with Examples. I hope this MySQL SUBSTR 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.