MySQL LOCATE() Function

---

---

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

It takes a string value and a substring as parameters and returns the position of the first occurrence of the specified substring in the original string. If the substring is not found within the original string, it returns 0 as the output.

It is important to note that this function performs a case-insensitive search.

Syntax

Following is the syntax of MySQL LOCATE() function −

LOCATE(substr, str);

Parameters

This function takes a substring and a string to search as a parameter.

Return Value

This function returns the position of the first occurrence of the substring within the string or 0 if not found.

Another Syntax

The function also has a second syntax where, in addition to the string values, it accepts another numerical value as a parameter, known as pos. It returns the position of the first occurrence of the specified substring in the original string, starting from the position pos −

LOCATE(substr,str,pos);

Example

In the following example, we are using the LOCATE() function to find the position of the substring 'how' within the given string −

SELECT LOCATE('how', 'Hello how are you');

Following is the output of the above code −

LOCATE('how', 'Hello how are you')
7

Example

Following example demonstrates the usage of the second syntax of the LOCATE() function. Here, we specify the pos value as 5, and it returns the position of the substring 'point' starting from the 5th position −

SELECT LOCATE('point', 'Tutorialspoint', 5);

Output of the above code is as shown below −

LOCATE('point', 'Tutorialspoint', 5)
10

Example

You can also pass numerical values as arguments to the first parameter in the form of a string −

SELECT LOCATE('112', '6522 7262 8972 92 112', 5);

The output obtained is as follows −

LOCATE('112', '6522 7262 8972 92 112', 5)
19

Example

If the substring provided is not found within the given string, the function returns 0 −

SELECT LOCATE('unknown', 'Tutorialspoint');

The result produced is as follows −

LOCATE('unknown', 'Tutorialspoint')
0

Example

If any of the arguments passed to this function is NULL, it returns NULL −

SELECT LOCATE(NULL, 'Tutorialspoint');

Following is the output of the above code −

LOCATE(NULL, 'Tutorialspoint')
NULL

Example

Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE EMP(
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   INCOME FLOAT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP VALUES 
('Krishna', 'Sharma', 19, 2000),
('Raj', 'Kandukuri', 20, 7000),
('Ramya', 'Ramapriya', 25, 5000),
('Mac', 'Mohan', 26, 2000);

The EMP obtained is as follows −

FIRST_NAME	LAST_NAME	AGE	INCOME
Krishna	Sharma	19	2000
Raj	Kandukuri	20	7000
Ramya	Ramapriya	25	5000
Mac	Mohan	26	2000

Following query searches for 'ma' in the 'LAST_NAME' column of the EMP table −

SELECT FIRST_NAME, LAST_NAME, AGE, LOCATE('ma', LAST_NAME) as result
FROM EMP;

After executing the above code, we get the following output −

FIRST_NAME	LAST_NAME	AGE	result
Krishna	Sharma	19	5
Raj	Kandukuri	20	0
Ramya	Ramapriya	25	3
Mac	Mohan	26	0