MID() function in MySQL

MID() :

This function in MySQL is used to extract a substring from a given input string. If the starting position is a positive number, then the substring of the given length will be extracted from the starting index. If negative, then the substring of the given length will be extracted from the ending index.

Syntax :

MID(str,pos,len)

Parameters :

This function accepts 3 parameters.

• str –
  A string from which we want to extract a substring.
• pos –
  It indicates the position in the input string from where extraction will be started.
• len –
  It indicates the length of the string which we want to extract.

Returns : 
It extracts a substring from a given input string. 

Example-1 : 

Extracting a string of length 5 from the string ‘geeksforgeeks’ with the help of MID Function starting from pos 1.

SELECT MID('geeksforgeeks', 1, 5) As SUBSTRING;

Output :

SUBSTRING
geeks

Example-2 :

Extracting a string of length 3 from the string ‘Learning MySQL is fun’ with the help of MID Function starting from pos -3.

SELECT MID('Learning MySQL is fun', -3, 3) As SUBSTRING;

Output :

SUBSTRING
fun

Example-3 :

MID Function can also be used on column data. 

Creating a Student table –

CREATE TABLE StudentDetails
(
Student_id INT AUTO_INCREMENT,  
Student_name VARCHAR(100) NOT NULL,
Roll INT NOT NULL,
Department VARCHAR(10) NOT NULL,
PRIMARY KEY(Student_id )
);

Inserting values into the table –

INSERT INTO StudentDetails
(Student_name ,Roll, Department )
VALUES
('Anik Biswas ',10100,'CSE'),
('Bina Mallick', 11000,'ECE' ),
('Niket Sharma', 12000,'IT' ),
('Sayan Samanta',13000, 'ME'  ),
('Riya Shah ', 14000,'EE' ),  
('Bipin Kohli', 15000,'CE' );

The table will look as follows.

SELECT  * from StudentDetails;

STUDENT_ID	STUDENT_NAME	ROLL	DEPARTMENT
1	Anik Biswas	10100	CSE
2	Bina Mallick	11000	ECE
3	Niket Sharma	12000	IT
4	Sayan Samanta	13000	ME
5	Riya Shah	14000	EE
6	Bipin Kohli	15000	CE

Now, we are going to use MID Function on the Student_name column to find the first name of every student.

SELECT Student_id , MID(Student_name,1,5 ) AS First_Name,
Student_name ,Roll,Department FROM StudentDetails;

Output :

STUDENT_ID	FIRST_NAME	STUDENT_NAME	ROLL	DEPARTMENT
1	Anik	Anik Biswas	10100	CSE
2	Bina	Bina Mallick	11000	ECE
3	Niket	Niket Sharma	12000	IT
4	Sayan	Sayan Samanta	13000	ME
5	Riya	Riya Shah	14000	EE
6	Bipin	Bipin Kohli	15000	CE