MySQL8 LOCATE() Functions – String Functions

by · Published · Updated

LOCATE() Functions returns the position of the first instance of srchstring in string or the first instance after a given initial position in the string.

Returns 0 if srchstring is not in string. Returns NULL if any argument is NULL.

The first syntax is the same as the  of INSTR(), except that the order of the arguments is opposite.

Note : This function is equal to the POSITION() function.

Note : This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

MySQL LOCATE() Functions: Syntax

LOCATE ( srchstring, string  );
OR
LOCATE ( srchstring, string , position);

MySQL LOCATE() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|left|left|left”]
Name, Required /Optional,Type, Description
string, Required, String , It represents the valid input string.
srchstring, Required, String , It represents valid srchstring to be search for.
position, Optional, Numeric , It represents starting position to start searching in the string.
[/table]

MySQL LOCATE() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Return, Description
Number, the position of the first instance of srchstring in string.
0, If srchstring is not found in the string.
NULL, if any argument is NULL.
[/table]

MySQL LOCATE() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

LOCATE() Example 1 : Basic Usage for First syntax

Below is the example to show it.

mysql> SELECT LOCATE('mines','This is tutorialmines site for all!');
+-------------------------------------------------------+
| LOCATE('mines','This is tutorialmines site for all!') |
+-------------------------------------------------------+
|                                                    17 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

LOCATE() Example 2 : Basic Usage for Second syntax

Below is the example to show it. Ihave added start position to be 6. So, searching will start from this position.

mysql> SELECT LOCATE('site','This is tutorialmines site for all!',6);
+--------------------------------------------------------+
| LOCATE('site','This is tutorialmines site for all!',6) |
+--------------------------------------------------------+
|                                                     23 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Below is the example to show it. I have added start position to be 26. So,searching will start from this position.’site’ is before 26 characters, and its not found in the input string.

mysql>  SELECT LOCATE('site','This is tutorialmines site for all!',26);
+---------------------------------------------------------+
| LOCATE('site','This is tutorialmines site for all!',26) |
+---------------------------------------------------------+
|                                                       0 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LOCATE() Example 3 : Locating Part of a Word

Below is the example where we will find ‘mines‘, in the word ‘tutorialmines‘ to show it.

mysql> SELECT LOCATE('mines','This is tutorialmines site for all!');
+-------------------------------------------------------+
| LOCATE('mines','This is tutorialmines site for all!') |
+-------------------------------------------------------+
|                                                    17 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

LOCATE() Example 4 : No Matches

If the srchstring isn’t found, 0 is returned:

mmysql> SELECT LOCATE('java','This is tutorialmines site for all!');
+------------------------------------------------------+
| LOCATE('java','This is tutorialmines site for all!') |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+
1 row in set (0.00 sec)

LIKE() Example 5 : Case Sensitivity

This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

That’s why the below works on nonbinary strings, even though the case doesn’t match:

mysql> SELECT LOCATE('Mines','tutorialmines!');
+----------------------------------+
| LOCATE('mines','tutorialmines!') |
+----------------------------------+
|                                9 |
+----------------------------------+
1 row in set (0.00 sec)

Below is the example, how it will work on nonbinary strings, even though the case doesn’t match:

mysql> SET @strb = BINARY 'tutorialmines'; 
       SELECT LOCATE('Mine', @strb) AS Output;
Query OK, 0 rows affected (0.00 sec)
+--------+
| Output |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Below is the example, if we change the case, we get a match:

mysql> SET @str = BINARY 'tutorialmines'; 
       SELECT LOCATE('mine', @str) AS Output; 
Query OK, 0 rows affected (0.00 sec)
+--------+
| Output |
+--------+
|      9 |
+--------+
1 row in set (0.00 sec)

LIKE() Example 6 : NULL arguments

If any of the arguments is NULL, it will return NULL. See below example

mysql> SELECT LOCATE('This is tutorialmines site for all!',NULL) as First,
       LOCATE(NULL,'mines') as Second, 
       LOCATE('This is tutorialmines site for all!','mines',NULL) as Third;
+-------+--------+-------+
| First | Second | Third |
+-------+--------+-------+
|  NULL |   NULL |     0 |
+-------+--------+-------+
1 row in set (0.00 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : HEX(), CONCAT(), CONCAT_WS() , LOWER(),  LTRIM(), INSTR(), POSITION().

PHP Related articles : SUBSTR(), SUBSTR_COUNT(), SUBSTR_COMPARE(), SUBSTR_REPLACE(), STRCMP(),  PHP STRING FUNCTIONS().

You may also like...