MySQL MID Function

Back to: MySQL Tutorials for Beginners and Professionals

MySQL MID Function with Examples

In this article, I am going to discuss MySQL MID Function with Examples. Please read our previous article where we discussed MySQL UCASE and LCASE Functions with Examples.

MySQL MID Function:

The MID function extracts characters from the given string data. You can also say that the MySQL MID() function extracts a substring from a string starting at any position. If the starting position is a positive number, then the substring of the given length will be extracted from the starting index. If the starting position is negative, then the substring of the given length will be extracted from the ending index. Following is the syntax to use the MID function.

MID(string, start, length)

Parameter

1. string: Required. The string to extract from
2. start: Required. The start position. Can be both a positive or negative number. If it is a positive number, this function extracts from the beginning of the string. If it is a negative number, this function extracts from the end of the string
3. length: Optional. The number of characters to extract. If not specified, then it will extract all the characters from the start position.

Example: Extract a substring from a string starting from position 7 and extract 3 characters
SELECT MID(“MySQL Tutorials”, 7, 3) AS ExtractString;
Output: Tut

Example: Extract a substring from a string starting from position 3 and extract all characters. Here, we don’t require to pass the length parameter.
SELECT MID(“MySQL Tutorials”, 7, 3) AS ExtractString;
Output: SQL Tutorials

Example: Extract a substring from a string starting from the end, at position -9 and extract 5 characters
SELECT MID(“MySQL Tutorials”,-9, 5) AS ExtractString;
Output: Tutor

Using the FROM Clause in MID Function

Here’s how to use the FROM Clause to do the same thing, but this time using the MID(string FROM position) syntax:
SELECT MID(‘DOT NET TUTORIALS’ FROM 4) Result;
Output: NET TUTORIALS

Specify Length using the FOR Clause:

In the below example, we use MID(str FROM position FOR length) syntax:
SELECT MID(‘DOT NET TUTORIALS’ FROM 4 FOR 4) Result;
Output: NET

Working with Database Tables:

The MID function only works on string columns. Following is the syntax to use the MySQL MID function,

Syntax: SELECT MID (columnname, start, length) FROM tablename;

where, Start: starting position, and Length: is optional and returns the number of characters to specified. If not defined returns the rest of the characters from start.

Understanding MID Function in MySQL with Database Table:

We are going to use the following Employee table to understand the MySQL MID function 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, from the Employee table, from the name column, let’s only get the first 3 letters of each employee. Following is the SQL Script to fetch the first three characters from the Name column of the Employee table.

SELECT Id, MID(Name, 1, 3) AS Name, Department, City FROM Employee;

In the above SQL statement, we will get the name of each employee starting from first characters and up to only 3 characters long. Now run the SQL statement, and you will get the following output.

If we don’t define the third parameter that is characters length, the MID function will return all the characters from the starting position. Modify the SQL statement as shown below where we remove the length parameter.

SELECT Id, MID(Name, 3) AS Name, Department, City FROM Employee;

Now to execute the SQL statement and you should get the following output. And this time we get all the name characters starting from the third position of the name column for each employee.

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