LOCATE

Example

Find the position of “3” in the string “W3Schools.com” and return its index.

SELECT LOCATE(“3”, “W3Schools.com”) AS MatchPosition;

Definition and Usage

The LOCATE() function identifies the position of the first instance of a substring within a string. If the substring isn’t found, it returns 0. It conducts a case-insensitive search.

Note that this function is synonymous with the POSITION() function.

Syntax

LOCATE(substring, string, start)

Parameter Values

Parameter	Description
substring	Input required: The substring to locate within the string.
string	Input required: The string in which the search for the substring will be performed.
start	Optional: You can specify the starting position for the search. If not specified, the search begins from position 1 by default.

Technical Details

Works in:

From MySQL 4.0

More Examples

Example

Find the position of “com” in the string “W3Schools.com” starting from position 3, and return its index.

SELECT LOCATE(“com”, “W3Schools.com”, 3) AS MatchPosition;

Example

Find the position of the character “a” in the CustomerName column, and return its index.

SELECT LOCATE(“a”, CustomerName)
FROM Customers;