MySQL LOCATE() Function Examples
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 :
MATCHLOCATION |
---|
6 |
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 :
MATCHLOCATION |
---|
0 |
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 :
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 by using LOCATE function on table column.
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 |
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.