Skip to content
Related Articles

Related Articles

Improve Article

LOCATE() function in MySQL

  • Last Updated : 17 Dec, 2020

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_IDSTUDENT_NAMEROLLDEPARTMENT
1Anik Biswas10100CSE
2Bina Mallick11000ECE
3Aniket Sharma12000IT
4Sayani Samanta13000ME
5Riyanka Shah14000EE
6Bipin Kohli15000CE

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_IDSTUDENT_NAMEROLLDEPARTMENTFirstOccurrenceOfA
1Anik Biswas10100CSE1
2Bina Mallick11000ECE4
3Aniket Sharma12000IT1
4Sayani Samanta13000ME2
5Riyanka Shah14000EE4
6Bipin Kohli15000CE0
My Personal Notes arrow_drop_up
Recommended Articles
Page :