MySQL LOCATE() Function

  1. Overview
  2. MYSQL
  3. String Functions
  4. MySQL LOCATE() Function

The LOCATE() function in MySQL is used to find the position of the first occurrence of a substring within a string.

Syntax

LOCATE(substring, string, start)

Parameters

Parameter Description
substring Required. The substring to search for in the string.
string Required. The string where the search will be performed.
start Optional. The position to start searching from (default is 1).

Definition and Use

  • Purpose: Returns the position of the first occurrence of a substring within a string.
  • Behavior:
    • If the substring is not found, the function returns 0.
    • The function performs a case-insensitive search.
    • The start parameter allows for specifying a starting position for the search. The default position is 1 (i.e., the beginning of the string).

Usage Examples

Example 1: Basic Usage

Find the position of "3" in the string "W3Schools.com":

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

Output:

MatchPosition
2

Explanation: "3" appears at position 2 in "W3Schools.com".

Example 2: Search Starting from a Specific Position

Search for "com" starting at position 3 in the string "W3Schools.com":

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

Output:

MatchPosition
11

Explanation: "com" is found starting at position 11 in "W3Schools.com", considering the search starts from position 3.

Example 3: Search for a Substring in a Column

Search for "a" in the CustomerName column and return the position of the first occurrence:

SELECT CustomerName, LOCATE("a", CustomerName) AS PositionOfA
FROM Customers;

Output (example data):

CustomerName PositionOfA
John Smith 2
Alice Brown 1

Explanation: The position of "a" in each customer name is displayed.

Technical Details

  • Availability: Available from MySQL 4.0 onwards.
  • Return Type: Returns an integer representing the position of the substring, or 0 if not found.
  • Case Sensitivity: The search is case-insensitive.

Applications

  1. Substring Positioning: Useful for locating substrings within strings for data extraction or transformation.
  2. Text Analysis: Helps in identifying the occurrence of certain words or characters within larger text fields.
  3. Data Validation: Can be used to check if certain substrings exist within fields (e.g., checking email domains or specific keywords).

Related Functions

  • POSITION(): Essentially the same as LOCATE() in MySQL, with the same syntax and behavior.
  • INSTR(): Finds the position of a substring within a string, but with a slightly different syntax.

Key Notes

  • The start parameter allows for skipping the first N characters in the search. If omitted, it defaults to position 1 (start of the string).
  • The function returns the position based on 1-indexing (not 0-indexing), meaning the first character of the string is at position 1.
  • If substring or string is NULL, the function returns NULL.

The LOCATE() function is a useful tool for searching and finding the position of substrings in MySQL, especially when you need precise control over the search process, including starting positions.

Was this article helpful?