POSITION() function in MySQL

POSITION() :

This function in MySQL is used for finding the location of a substring in a string. It will return the location of the first occurrence of the substring in the string. If the substring is not present in the string then it will return 0. When searching for the location of a substring in a string then the function does not perform a case-sensitive search.

Syntax :

POSITION(substring IN string)

Parameters :

This method accepts two parameters 

• substring – The string whose position is to be retrieved.
• string – The string within which the position of the substring is to be retrieved.

Returns :

The location of the first occurrence of the substring in the string.

Example-1 : 

Searching the String ‘g’ in the string ‘geeksforgeeks’ with the help of the POSITION Function as follows.

SELECT POSITION('g' IN 'geeksforgeeks') AS location;

Output :

LOCATION
1

Example-2 :

Searching the String ‘this’ in the string ‘That is a tree’ with the help of the POSITION Function as follows.

SELECT POSITION('this' IN 'That is a tree') AS location;

Output :

LOCATION
0

Example-3 :

POSITION Function can also be used on column data as follows.  

Creating a Student table :

CREATE TABLE Student
(
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 data into the Table :

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

To verify used the following command as follows.

SELECT  * from Student ;

Output :

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

Now we are going to find the first occurrence of the string ‘a’ for every student’s name.

SELECT *,POSITION('a' IN Student_name ) AS First_Occ_A  
FROM STUDENT;

Output :

STUDENT_ID	STUDENT_NAME	ROLL	DEPARTMENT	FIRST_OCC_A
1	Anik Biswas	10100	CSE	1
2	Bina Mallick	11000	ECE	4
3	Aniket Sharma	12000	IT	9
4	Sayani Samanta	13000	ME	2
5	Riyanka Shah	14000	EE	4
6	Bipin Kohli	15000	CE	0