LOCATE and INSTR in MySQL - Step-by-Step Execution

Concept Flow - LOCATE and INSTR

Start with string and substring

↓

Search substring in string

↓

Find first occurrence position

↓

Return position or 0 if not found

↓

End

LOCATE and INSTR find the position of a substring inside a string and return its first occurrence index or 0 if not found.

Execution Sample

MySQL

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

Find the position of 'cat' inside 'concatenate' using LOCATE and INSTR functions.

Execution Table

Step	Function	Input String	Substring	Position Found	Output
1	LOCATE	'concatenate'	'cat'	4	4
2	INSTR	'concatenate'	'cat'	4	4
3	LOCATE	'hello world'	'cat'	0	0
4	INSTR	'hello world'	'cat'	0	0
5	LOCATE	'banana'	'na'	3	3
6	INSTR	'banana'	'na'	3	3
7	LOCATE	'banana'	'na' (start=4)	5	5
8	INSTR	'banana'	'na'	3	3
9	LOCATE	'apple'	'p'	2	2
10	INSTR	'apple'	'p'	2	2
11	LOCATE	'apple'	'z'	0	0
12	INSTR	'apple'	'z'	0	0

💡 All searches complete; positions returned or 0 if substring not found.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6
Input String	'concatenate'	'concatenate'	'hello world'	'hello world'	'banana'	'banana'	'banana'
Substring	'cat'	'cat'	'cat'	'cat'	'na'	'na'	'na'
Position Found	null	4	0	0	3	3	3
Output	null	4	0	0	3	3	3

Key Moments - 3 Insights

Why does LOCATE return 0 when the substring is not found?

LOCATE returns 0 to indicate the substring does not exist in the string, as shown in execution_table rows 3 and 4.

What is the difference between LOCATE and INSTR in usage?

LOCATE allows an optional start position parameter (see row 7), while INSTR always searches from the start. Both return the first occurrence position.

Why does LOCATE('na', 'banana', 4) return 5 instead of 3?

Because LOCATE starts searching from position 4, it finds the next 'na' at position 5, as shown in execution_table row 7.

Visual Quiz - 3 Questions

Look at the execution_table row 1. What position does LOCATE return for 'cat' in 'concatenate'?

A 4

B 3

C 0

D 5

💡 Hint

Check the 'Position Found' and 'Output' columns in row 1.

At which step does LOCATE start searching from a position other than 1?

A Step 2

B Step 7

C Step 9

D Step 11

💡 Hint

Look for the row where LOCATE has a third parameter (start position).

If the substring is not found, what do LOCATE and INSTR return according to the table?

A NULL

B -1

C 0

D 1

💡 Hint

See rows 3, 4, 11, and 12 for outputs when substring is missing.

Concept Snapshot

LOCATE(substring, string[, start]) and INSTR(string, substring) find the first position of substring in string.
They return the position as a number starting at 1.
If substring not found, they return 0.
LOCATE can start searching from a given position; INSTR always starts at 1.
Useful for finding where text appears inside other text.

Full Transcript

LOCATE and INSTR are MySQL functions to find the position of a substring inside a string. LOCATE takes the substring first, then the string, and optionally a start position to begin searching. INSTR takes the string first, then the substring, and always searches from the start. Both return the position of the first occurrence starting at 1, or 0 if the substring is not found. For example, LOCATE('cat', 'concatenate') returns 4 because 'cat' starts at the 4th character. If the substring is missing, like LOCATE('cat', 'hello world'), it returns 0. LOCATE can also start searching from a specific position, like LOCATE('na', 'banana', 4) returns 5, finding the second 'na'. These functions help find where text appears inside other text strings.

Practice

1. What does the MySQL function LOCATE('cat', 'concatenate') return?

easy

A. 0

B. 4

C. 1

D. 7

Solution

1. Step 1: Understand LOCATE function behavior

   LOCATE returns the position of the first occurrence of the substring in the string, starting from the beginning by default.

2. Step 2: Find 'cat' in 'concatenate'

   The substring 'cat' starts at the 4th character in 'concatenate'.

3. Final Answer:

   4 -> Option B

4. Quick Check:

   LOCATE('cat', 'concatenate') = 4 [OK]

Hint: LOCATE returns first position of substring, counting from 1 [OK]

Common Mistakes:

• Counting from 0 instead of 1
• Confusing LOCATE with INSTR starting position
• Assuming substring not found returns NULL

2. Which of the following is the correct syntax to find the position of 'dog' in the string 'bulldog' starting search from position 5 using LOCATE?

easy

A. LOCATE('dog', 'bulldog') FROM 5

B. LOCATE('dog', 5, 'bulldog')

C. LOCATE('bulldog', 'dog', 5)

D. LOCATE('dog', 'bulldog', 5)

Solution

1. Step 1: Recall LOCATE syntax

   The correct syntax is LOCATE(substring, string, start_position).

2. Step 2: Match syntax with options

   LOCATE('dog', 'bulldog', 5) matches the correct order: substring 'dog', string 'bulldog', start position 5.

3. Final Answer:

   LOCATE('dog', 'bulldog', 5) -> Option D

4. Quick Check:

   LOCATE(substring, string, start) correct syntax [OK]

Hint: LOCATE syntax: substring, string, start position [OK]

Common Mistakes:

• Swapping string and substring order
• Using incorrect argument order
• Trying to use FROM keyword

3. What is the output of this query?

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

medium

A. 6

B. 8

C. 0

D. 5

Solution

1. Step 1: Understand INSTR function behavior

   INSTR returns the position of the first occurrence of the substring in the string, starting from the beginning.

2. Step 2: Find 'o' in 'hello world'

   The first 'o' appears at the 6th character in 'hello world'.

3. Final Answer:

   6 -> Option A

4. Quick Check:

   INSTR('hello world', 'o') = 6 [OK]

Hint: INSTR always starts searching from beginning [OK]

Common Mistakes:

• Choosing position of second 'o' instead of first
• Confusing INSTR with LOCATE with start position
• Assuming zero-based index

4. Identify the error in this query:

SELECT LOCATE('test', 'testing', 0);

medium

A. Start position cannot be zero; must be >= 1

B. String and substring arguments are swapped

C. No error; query is valid

D. LOCATE does not accept three arguments

Solution

1. Step 1: Check LOCATE start position rules

   The start position argument can be less than 1; it is treated as 1 with no error.

2. Step 2: Analyze the query

   The query uses 0 as start position, which is valid; the search starts from position 1.

3. Final Answer:

   No error; query is valid -> Option C

4. Quick Check:

   LOCATE start position <1 clamped to 1 [OK]

Hint: LOCATE start_position <1 is allowed, search starts from 1 [OK]

Common Mistakes:

• Believing start position must be >=1 (it's clamped if less)
• Confusing argument order
• Assuming LOCATE uses zero-based indexing

5. Given the string 'abracadabra', which query returns the position of the second occurrence of 'a' using LOCATE?

hard

A. SELECT LOCATE('a', 'abracadabra', LOCATE('a', 'abracadabra') + 1);

B. SELECT INSTR('abracadabra', 'a', 2);

C. SELECT LOCATE('a', 'abracadabra', 5);

D. SELECT LOCATE('a', 'abracadabra', 1);

Solution

1. Step 1: Find first occurrence of 'a'

   LOCATE('a', 'abracadabra') returns 1, the position of the first 'a'.

2. Step 2: Use LOCATE with start position after first 'a'

   To find the second 'a', start searching from position 2 (1 + 1) using LOCATE('a', 'abracadabra', 2).

3. Step 3: Combine steps in one query

   Use nested LOCATE: LOCATE('a', 'abracadabra', LOCATE('a', 'abracadabra') + 1) to get second occurrence.

4. Final Answer:

   SELECT LOCATE('a', 'abracadabra', LOCATE('a', 'abracadabra') + 1); -> Option A

5. Quick Check:

   Nested LOCATE finds second occurrence [OK]

Hint: Use nested LOCATE with start = first position + 1 [OK]

Common Mistakes:

• Using INSTR with start position (not supported)
• Starting search at wrong position
• Not adding 1 to first occurrence position