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 :

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 :

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 :

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 :

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql