MySQL LOCATE() Function
The LOCATE() function in MySQL helps you find the position of a substring within a larger string. It's a fundamental tool for text processing and data manipulation.
LOCATE(): Definition and Usage
LOCATE() searches for a substring within a given string and returns the starting position of the first occurrence of that substring. If the substring isn't found, it returns 0. The search is case-insensitive. LOCATE() is identical to the POSITION() function.
Syntax
Syntax
LOCATE(substring, string, start)
Parameter Values
| Parameter | Description |
|---|---|
substring |
The substring to search for. This is required. |
string |
The string to search within. This is required. |
start (Optional) |
The starting position for the search (default is 1). |
Examples
Finding the Position of a Substring
This example searches for the substring "3" within the string "W3Schools.com".
Syntax
SELECT LOCATE("3", "W3Schools.com") AS MatchPosition;
Output
3
Specifying a Starting Position
This example searches for "com" within "W3Schools.com", but starts the search at the third position.
Syntax
SELECT LOCATE("com", "W3Schools.com", 3) AS MatchPosition;
Output
10
Finding the Position of a Substring in a Column
This example searches for "a" within the 'CustomerName' column of the 'Customers' table (assuming a 'Customers' table exists with a 'CustomerName' column).
Syntax
SELECT LOCATE("a", CustomerName)
FROM Customers;
Output
(The position of the first 'a' in each CustomerName will be returned. 0 if 'a' is not present.)