THE WORLD'S LARGEST WEB DEVELOPER SITE

MySQL LOCATE() Function

❮ MySQL Functions

Example

Search for "3" in string "W3Schools.com", and return position:

SELECT LOCATE("3", "W3Schools.com") AS MatchPosition;
Try it Yourself »

Definition and Usage

The LOCATE() function returns the position of the first occurrence of a substring in a string.

Note: The POSITION() function is a synonym for the LOCATE() function.

Syntax

LOCATE(substring, string, start)

Parameter Values

Parameter Description
substring Required. The substring to search for in string
string Required. The string that will be searched
start Optional. The starting position for the search. Position 1 is default

Note

  • LOCATE() performs a case-insensitive search
  • The first position in string is 1
  • If substring is not found within string, the LOCATE() function will return 0

Technical Details

Works in: MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

More Examples

Example

Search for "com" in string "W3Schools.com" (start at position 3), and return position:

SELECT LOCATE("com", "W3Schools.com", 3) AS MatchPosition;
Try it Yourself »

Example

Search for "a" in CustomerName column, and return position:

SELECT LOCATE("a", CustomerName)
FROM Customers;
Try it Yourself »

❮ MySQL Functions