LOCATE() FUNCTION in MySQL

LOCATE() FUNCTION
The MySQL LOCATE function is used to get the location of the first appearance of a substring in a string. The various versions of MySQL support the LOCATE function, namely, MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0 and MySQL 3.23.

Syntax:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
LOCATE( substring, string, start_position )
LOCATE( substring, string, start_position )
LOCATE( substring, string, start_position )

Parameters:
substring: It is used to specify the substring to search for.
string: It is used to specify the string to search.
start_position: It is an optional parameter that is used to specify the position to start the search from.

Example 1:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql> SELECT LOCATE ( ‘L'HELLO WORLD' );
mysql> SELECT LOCATE ( ‘L’ 'HELLO WORLD' );
mysql> SELECT LOCATE ( ‘L’ 'HELLO WORLD' );

Output:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
3
3
3

Explanation:
The position of the first appearance of the substring starting from the first position is returned as the result.

Example 2:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql> SELECT LOCATE ( ‘l’ 'HELLO WORLD', 5 );
mysql> SELECT LOCATE ( ‘l’ 'HELLO WORLD', 5 );
mysql> SELECT LOCATE ( ‘l’ 'HELLO WORLD', 5 );

Output:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
10
10
10

Explanation:
The position of the first appearance of the substring starting from the 5th position is returned as the result.