MySQL LOCATE() Function

 The LOCATE() function in MySQL is used to find the position of a substring within a string. It returns the position of the first occurrence of the substring. If the substring is not found, the function returns 0.

Syntax:

LOCATE(substring, string, start_position)

• substring: The substring to search for.
• string: The string in which to search.
• start_position (optional): The position to start the search (1-based index). If omitted, the search starts from the beginning.

Example 1:

Find the position of the substring "SQL" in the string "Learn MySQL":

SELECT LOCATE('SQL', 'Learn MySQL');

Result: 7
Explanation: The substring "SQL" starts at position 7 in the string "Learn MySQL".

Example 2:

Find the position of the substring "o" in the string "Hello World" starting from position 5:

SELECT LOCATE('o', 'Hello World', 5);

Result: 8
Explanation: The first occurrence of "o" after position 5 is at position 8.

Example 3:

If the substring is not found:

SELECT LOCATE('XYZ', 'Hello World');

Result: 0
Explanation: The substring "XYZ" does not exist in "Hello World", so the result is 0.

Notes:

• The position returned by LOCATE() is 1-based. So, the first character of the string is at position 1.
• If the start_position is greater than the length of the string, LOCATE() returns 0.