How to Use LOCATE Function in MySQL: Syntax and Examples

In MySQL, use the LOCATE(substring, string, start_position) function to find the position of a substring inside a string. It returns the position as an integer or 0 if the substring is not found. The start_position is optional and defaults to 1.

Syntax

The LOCATE function syntax is:

• substring: The text you want to find.
• string: The text where you search.
• start_position (optional): The position to start searching from (default is 1).

The function returns the position of the first occurrence of substring in string starting from start_position. If not found, it returns 0.

sql

LOCATE(substring, string, start_position)

Example

This example shows how to find the position of 'cat' in the string 'concatenate'. It also shows how to start searching from a specific position.

sql

SELECT LOCATE('cat', 'concatenate') AS position1, LOCATE('cat', 'concatenate', 5) AS position2;

Output

position1 | position2
----------|---------
4         | 7

Common Pitfalls

Common mistakes include:

• Forgetting that LOCATE returns 0 if the substring is not found, not NULL.
• Using a start_position less than 1, which is treated as 1.
• Confusing LOCATE with INSTR which has similar behavior but different argument order in some databases.

sql

/* Wrong: expecting NULL when substring not found */
SELECT LOCATE('dog', 'concatenate') AS wrong_result;

/* Right: returns 0 when not found */
SELECT LOCATE('dog', 'concatenate') AS correct_result;

Output

wrong_result
0

correct_result
0

Quick Reference

Parameter	Description
substring	Text to find inside the string
string	Text to search within
start_position	Optional start position for search (default 1)
Return value	Position of substring or 0 if not found

Key Takeaways

Use LOCATE(substring, string, start_position) to find substring position in MySQL.

LOCATE returns 0 if the substring is not found, never NULL.

The start_position parameter is optional and defaults to 1.

Positions start counting at 1, not 0.

Avoid using start_position less than 1 as it defaults to 1.