MySQL LOCATE()

In SQL, the LOCATE() function is used to search for the position of a string in a record. The function returns zero if the string has not been found.

This function can be very useful when coupled with the SUBSTR() function ( alias SUBSTRING() ) to replace characters in a string.

The LOCATE() function is similar to the PHP strpos() function.
 

 

Syntax :

The function can be used in a SQL query such as this one:

SELECT LOCATE(string_to_find, text);

In this query, the LOCATE() function will look for the content of “string_to_find” in “text”. If the string exists and is present, then the function will return the position of the first occurrence.

Note: the first character is at position 1 and if the string is not found, the function will return 0.

It is possible to search for a string by starting to search from a position. The following SQL query allows you to search for the string “test” in the column “column_name” by starting to search from position 5.

SELECT LOCATE('test', column_name, 5);

This 3rd parameter is optional and can be used if you want to ignore the first part.
 

Example :

Let’s suppose a table that contains the different units related to meters (kilometer, millimeter, centimeter …).

+----------+------------+
|    id    |    unit    |
+----------+------------+
|    1     | kilometer  |
|    2     | millimeter |
|    3     | meter      |
|    4     | centimeter |
|    5     | mm         |
+----------+------------+

 

 
In this tutorial, we will try to get the position of the word “meter” in the “unite” column. For this purpose it is possible to execute the following query:

SELECT `id`, `unit`, LOCATE(`metre`, unit) As locate_unit
FROM `metre`

Output:

+----------+------------+-----------------+
|    id    |    unit    |   locate_unit   |
+----------+------------+-----------------+
|    1     | kilometer  | 5               |
|    2     | millimeter | 6               |
|    3     | meter      | 1               |
|    4     | centimeter | 6               |
|    5     | mm         | 0               |
+----------+------------+-----------------+

The results show that it is possible to get the position of the searched word and the function returns zero if the string has not been found.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More