LOCATE()

Function Description

The LOCATE() function is a function used to find the location of a substring in a string. It returns the position of the substring in the string and if not found, 0.

Since the LOCATE() function returns an integer value, it can be nested in other functions, such as using the substring function to intercept strings.

Regarding case, the LOCATE() function is case-insensitive.

Function Syntax

> LOCATE(subtr,str,pos)

Parameter definition

Parameters	Description
substr	Required parameters. substring is the string you are looking for.
str	Required parameters. string is the string to search for.
pos	Non-essential parameters. position is the location where the query started.

Example

• Example 1

mysql> SELECT LOCATE('bar', 'footbarbar');
+------------------------------+
| locate(bar, footbarbar) |
+------------------------------+
| 5 |
+------------------------------+
1 row in set (0.00 sec)

• Example 2

mysql>SELECT LOCATE('bar', 'footbarbar',6);
+----------------------------------+
| locate(bar, footbarbar, 6) |
+----------------------------------+
| 8 |
+----------------------------------+
1 row in set (0.00 sec)

• Example 3

mysql>SELECT SUBSTRING('hello world',LOCATE('o','hello world'),5);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| substring(hello world, locate(o, hello world), 5) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| o wor |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

• Example 4

mysql>select locate('a','ABC');
+---------------------+
| locate(a, ABC) |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)