LOCATE and INSTR in MySQL

Introduction

LOCATE and INSTR help find where a smaller piece of text appears inside a bigger text. This is useful to check if a word or letter is inside a sentence and where it starts.

You want to find the position of a word in a sentence stored in a database.

You need to check if a certain character exists in a text column.

You want to extract or work with parts of text based on where a word appears.

You want to filter rows where a substring appears in a column.

You want to count or analyze text data by locating specific patterns.

Syntax

MySQL

LOCATE(substring, string[, start_position])
INSTR(string, substring)

LOCATE returns the position of the first occurrence of substring in string, starting at start_position if given (default is 1).

INSTR returns the position of the first occurrence of substring in string, but does not support a start position argument.

Examples

Finds where 'cat' starts in 'concatenate'. Result is 4 because 'cat' starts at the 4th letter.

MySQL

SELECT LOCATE('cat', 'concatenate');

Starts searching for 'cat' from the 5th letter. Result is 0 because 'cat' does not appear after position 5.

MySQL

SELECT LOCATE('cat', 'concatenate', 5);

Finds where 'cat' starts in 'concatenate'. Result is 4, same as LOCATE without start position.

MySQL

SELECT INSTR('concatenate', 'cat');

Searches for 'z' in 'hello world'. Result is 0 because 'z' is not found.

MySQL

SELECT INSTR('hello world', 'z');

Sample Program

This query finds the position of 'day' in the sentence using both LOCATE and INSTR functions.

MySQL

SELECT
  LOCATE('day', 'Today is a sunny day') AS locate_result,
  INSTR('Today is a sunny day', 'day') AS instr_result;

Important Notes

Both LOCATE and INSTR return 0 if the substring is not found.

LOCATE allows you to specify where to start searching, INSTR does not.

Positions start at 1, not 0, meaning the first character is position 1.

Summary

LOCATE and INSTR find the position of a substring inside a string.

LOCATE can start searching from a specific position; INSTR always starts at the beginning.

If the substring is not found, both return 0.