MySQL LOCATE() Function

MySQL LOCATE() function is used to find the position of a substring in a string.

It returns 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. LOCATE() function in MySQL does not perform a case-sensitive search.

MySQL LOCATE function() Syntax is:

LOCATE(substring, string, start)

Parameters

The LOCATE() function accepts three parameters.

• 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.

Let’s look at some examples of the LOCATE() function in MySQL. Learning the MySQL LOCATE function with examples will help you understand it better.

Example 1

In this example, we are searching the String ‘f’ in the string ‘w3wiki’ with the help of LOCATE Function.

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

Output :

Example 2

In this example, we are 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 :

Example 3

In this example, we are searching the String ‘g’ in the string ‘w3wiki’ with the help of LOCATE Function starting from position 3.

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

Output :

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 ;

Now, we will find the first occurrence of string ‘a’ in the Student_name by using LOCATE function on table column.

SELECT *, LOCATE('a', Student_name ) AS FirstOccurrenceOfA  
FROM STUDENT;