Open In App

LOCATE() function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

LOCATE() 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 it does not perform a case-sensitive search.

Syntax :

LOCATE(substring, string, start)

Parameters :

This method accepts three parameter.

  • substring –
    The string whose position is to be retrieved.
  • string –
    The string within which the position of the substring is to be retrieved.
  • start –
    The starting position for the search. It is optional .Position 1 is default.

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

Example-1 : Searching the String ‘f’ in the string ‘geeksforgeeks’ with the help of LOCATE Function.

SELECT LOCATE('f', 'geeksforgeeks') AS MatchLocation;

Output :

MATCHLOCATION
6

Example-2 : Searching the String ‘MYSQL’ in the string ‘Learning SQL is fun’ with the help of LOCATE Function.So, it will return 0.

SELECT LOCATE('MYSQL', 'Learning SQL is fun') AS MatchLocation;

Output :

MATCHLOCATION
0

Example-3 :Searching the String ‘g’ in the string ‘geeksforgeeks’ with the help of LOCATE Function starting from position 3.

SELECT LOCATE('g', 'geeksforgeeks', 3) AS MatchLocation;

Output :

MATCHLOCATION
9

Example-4 :
LOCATE Function can also be used on column data. To demonstrate create a table named Student.

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 some data to the Student table :

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

So, the Student Table is as follows.

SELECT  * from Student ;
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 will find the first occurrence of string ‘a’ in the Student_name column with the help of LOCATE function.

SELECT *, LOCATE('a', Student_name ) AS FirstOccurrenceOfA  
FROM STUDENT;
STUDENT_ID STUDENT_NAME ROLL DEPARTMENT FirstOccurrenceOfA
1 Anik Biswas 10100 CSE 1
2 Bina Mallick 11000 ECE 4
3 Aniket Sharma 12000 IT 1
4 Sayani Samanta 13000 ME 2
5 Riyanka Shah 14000 EE 4
6 Bipin Kohli 15000 CE 0

Last Updated : 17 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads