MySQL LOCATE() Function: Syntax, Start Position, and Examples

MySQL LOCATE(substr, str[, pos]) returns the 1-based position of the first occurrence of substr in str. It returns 0 if there is no match and NULL if an argument is NULL. The optional pos argument starts the search later in the string; see the MySQL LOCATE() reference.

The Anatomy of LOCATE()

The LOCATE() function comes in two flavors:

LOCATE(substr, str)
LOCATE(substr, str, pos)

Parameters Explained:

• substr: The needle you’re searching for
• str: The haystack where you’re searching
• pos (optional): Where to start searching (1-based index)

Key Behavior:

• Returns the position of the first occurrence
• Returns 0 if the substring isn’t found
• String matching follows the arguments’ collation. A binary argument makes matching case-sensitive.

Basic Example:

SELECT LOCATE('base', 'Database');  -- Returns 5

Everyday Search Scenarios

Finding Domain Names in Emails

SELECT
  email,
  LOCATE('@', email) AS at_position,
  SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

Validating String Formats

-- Check for properly formatted product codes (XX-999)
SELECT product_code
FROM products
WHERE LOCATE('-', product_code) = 3
  AND LENGTH(product_code) = 6;

Multi-occurrence Searching

-- Find second occurrence of 'error' in log
SELECT
  log_text,
  LOCATE('error', log_text, LOCATE('error', log_text) + 1) AS second_occurrence
FROM system_logs;

LOCATE() vs Other Search Functions

How LOCATE() compares to similar functions:

1. INSTR(): Same search with the string and substring arguments reversed

   LOCATE('needle', 'haystack')  -- Same as:
   INSTR('haystack', 'needle')
   

2. POSITION(): SQL-standard spelling for the two-argument search

   POSITION('needle' IN 'haystack')
   

3. LIKE: Better for pattern matching than exact position finding

4. REGEXP: More powerful but more complex for simple searches

For a concise comparison of the three substring-position forms, see 3 ways to find a substring’s position in MySQL.

Advanced Positioning Techniques

Nested Searching

-- Find text between parentheses
SELECT
  SUBSTRING(
    description,
    LOCATE('(', description) + 1,
    LOCATE(')', description) - LOCATE('(', description) - 1
  ) AS parenthetical
FROM product_descriptions;

Case-Sensitive Searching

For nonbinary strings, the collation determines whether letter case matters. This example makes the pattern binary so the comparison is case-sensitive:

-- Force case-sensitive search regardless of collation
SELECT LOCATE(CAST('mysql' AS BINARY), 'MySQL Database');  -- Returns 0

CAST(... AS BINARY) avoids the deprecated BINARY expr operator. See MySQL’s CAST reference and SQLiz’s MySQL warning 1287 guide.

Dynamic Position Calculation

-- Extract text after last slash in path
SELECT
  path,
  SUBSTRING(path,
    LOCATE('/', REVERSE(path)) + 1
  ) AS filename
FROM file_records;

Performance Considerations

While LOCATE() is generally efficient, watch for:

1. Large text columns: Scanning long TEXT/BLOB fields can be slow
2. Repeated searches: Consider storing positions if frequently needed
3. Index usage: Functions in WHERE clauses often prevent index usage

Optimization Example:

-- Less efficient (can't use index):
SELECT * FROM documents WHERE LOCATE('confidential', content) > 0;

-- Better for full-text search:
ALTER TABLE documents ADD FULLTEXT(content);
SELECT * FROM documents WHERE MATCH(content) AGAINST('confidential');

Real-World Use Cases

URL Processing

-- Extract query parameters
SELECT
  url,
  SUBSTRING(url, LOCATE('?', url) + 1) AS query_string
FROM web_requests
WHERE LOCATE('?', url) > 0;

Data Cleaning

-- Remove trailing semicolons
UPDATE imported_data
SET raw_value = SUBSTRING(raw_value, 1, LOCATE(';', raw_value) - 1)
WHERE LOCATE(';', raw_value) > 0;

Conditional Logic

-- Categorize based on content presence
SELECT
  message,
  CASE
    WHEN LOCATE('urgent', message) > 0 THEN 'High Priority'
    WHEN LOCATE('review', message) > 0 THEN 'Needs Attention'
    ELSE 'Normal'
  END AS priority
FROM notifications;

Handling Edge Cases

LOCATE() gracefully handles special situations:

1. Empty strings:

   SELECT LOCATE('', 'text');  -- Returns 1
   

2. NULL values:

   SELECT LOCATE('needle', NULL);  -- Returns NULL
   

3. Position overflow:

   SELECT LOCATE('text', 'search', 100);  -- Returns 0
   

4. Zero-length substr:

   SELECT LOCATE('', 'text', 3);  -- Returns 3
   

Summary

Use LOCATE() for an exact substring search that returns a character position. Use its optional pos argument to continue searching from a later point, and choose LIKE or a regular expression when you need pattern matching.