MySQL LOCATE() function

LOCATE() function

MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not mentioned, searching starts from the beginning.

This function is useful in -

• Substring position: It allows us to determine the position of a substring within a string.
• Boolean check: We can use LOCATE() as a Boolean check to determine if a substring exists within a string.
• Pattern matching: The LOCATE() function can be used to match patterns within strings.

Syntax:

LOCATE(substr,str)

LOCATE (search str, str, [position])

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL LOCATE() function

The following MySQL statement returns the 1st occurrence ‘st’ within the string ‘myteststring’. Since the "st" subsrting is found at the fifth position, the function returns 5.

Code:

SELECT LOCATE('st','myteststring'); 

Output:

mysql> SELECT LOCATE('st','myteststring'); 
+-----------------------------+
| LOCATE('st','myteststring') |
+-----------------------------+
|                           5 | 
+-----------------------------+
1 row in set (0.02 sec)

Example: MySQL LOCATE() function with starting position

The following statement returns the 1st occurrence ‘st’ within the string ‘myteststring’ and the searching will start from the 6th position of the string. The function returns 7, since the substring st is found at seventh position.

Code:

SELECT LOCATE('st','myteststring',6);

Output:

mysql> SELECT LOCATE('st','myteststring',6);
+-------------------------------+
| LOCATE('st','myteststring',6) |
+-------------------------------+
|                             7 | 
+-------------------------------+
1 row in set (0.00 sec)

Example: MySQL LOCATE() function using table

The following MySQL statement returns those rows from the publisher table where the search string ‘at’ exists at least once within the column pub_name.

Code:

SELECT pub_name,LOCATE('at',pub_name) 
FROM publisher 
WHERE locate('at',pub_name)>0; 

Sample table: publisher

Output:

mysql> SELECT pub_name,LOCATE('at',pub_name) 
    -> FROM publisher 
    -> WHERE locate('at',pub_name)>0;
+--------------------------+-----------------------+
| pub_name                 | LOCATE('at',pub_name) |
+--------------------------+-----------------------+
| Jex Max Publication      |                    15 | 
| BPP Publication          |                    11 | 
| New Harrold Publication  |                    19 | 
| Mountain Publication     |                    16 | 
| Summer Night Publication |                    20 | 
+--------------------------+-----------------------+
5 rows in set (0.03 sec)

Example: MySQL LOCATE() function with WHERE clause

The following MySQL statement returns those rows from the publisher table where the search string ‘at’ present at least once within the column pub_name. In this statement the 1st locate starts the searching from the beginning of the string and the second searching starts from the 16th position of the string.

Code:

SELECT pub_name,LOCATE('at',pub_name),
LOCATE('at',pub_name,16)
FROM publisher 
WHERE LOCATE('at',pub_name)>0;

Sample table: publisher

Output:

mysql> SELECT pub_name,LOCATE('at',pub_name),
    -> LOCATE('at',pub_name,16)
    -> FROM publisher 
    -> WHERE LOCATE('at',pub_name)>0;
+--------------------------+-----------------------+--------------------------+
| pub_name                 | LOCATE('at',pub_name) | LOCATE('at',pub_name,16) |
+--------------------------+-----------------------+--------------------------+
| Jex Max Publication      |                    15 |                        0 | 
| BPP Publication          |                    11 |                        0 | 
| New Harrold Publication  |                    19 |                       19 | 
| Mountain Publication     |                    16 |                       16 | 
| Summer Night Publication |                    20 |                       20 | 
+--------------------------+-----------------------+--------------------------+
5 rows in set (0.00 sec)

Video Presentation:

All String Functions (Slides presentation)

Previous: LOAD_FILE
Next: LOWER