MySQL LOCATE() function

In this context, we will learn how we can use the MySQL LOCATE() function with proper syntax and good examples.

Introduction of MySQL LOCATE() function

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 of the MySQL LOCATE() function

The syntax of the MySQL LOCATE() function is given as follows:

LOCATE(substr, str, start_pos)

Parameters or arguments used in MySQL LOCATE() function:

There are three parameters accepted by the LOCATE() function in MySQL, which are given as follows:

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

Returns:

It will return the location of the first occurrence of the substring in the string.

Application used for LOCATE() function:

The LOCATE() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL LOCATE() function:

Now we will look into some MySQL LOCATE() function examples and will explore how we can use the LOCATE function in MySQL.

Example-1:

Here, we will Search the String 't' in the string 'javatpoint' with the help of LOCATE Function.

SELECT LOCATE('t', 'javatpoint') AS MatchLocation;

Output:

MATCHLOCATION
5
1 row in set (0.00 sec)

Example-2 :

Here, we will Search '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
1 row in set (0.00 sec)

Example-3 :

Here, we will Search the String 'p' in the string 'javatpoint' with the help of LOCATE Function starting from position 3.

SELECT LOCATE('p,' 'javatpoint,' 3) AS MatchLocation;

Output :

MATCHLOCATION
6
1 row in set (0.00 sec)

Example-4 :

When we need to locate the column data string, we can take the help of the LOCATE function. To demonstrate, we have created a table named Pupil.

CREATE TABLE Pupil
(
Pupil_id INT AUTO_INCREMENT,
Pupil_name VARCHAR(100) NOT NULL,
Roll INT NOT NULL,
Department VARCHAR(10) NOT NULL,
PRIMARY KEY(Pupil_id )
);

Inserting some data to the Pupil table.

INSERT INTO Pupil
(Pupil_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 Pupil Table is as follows.

SELECT * from Pupil ;

PUPIL_ID	PUPIL_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 the string 'a' in the Pupil_name column with the help of LOCATE function.

SELECT *, LOCATE('a', Pupil_name ) AS FirstOccurrenceOfA
FROM PUPIL;

PUPIL_ID	PUPIL_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

Application of MySQL LOCATE() function:

This function is used to find a substring's location in a string.

Summary:

In the above context, we have learned how we can use the LOCATE() function in MySQL is used for finding the location of a substring in a string.