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 :

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 :

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;

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 :

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql