LOCATE and INSTR in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find position of substring using LOCATE

What is the output of this query?

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

MySQL

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

A 5

B 4

C 3

D 0

💡 Hint

LOCATE returns the position where the substring starts, counting from 1.

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

❓ query_result

intermediate

Difference between LOCATE and INSTR

What is the output of this query?

SELECT INSTR('database', 'a');

MySQL

SELECT INSTR('database', 'a');

A 0

B 1

C 2

D 3

💡 Hint

INSTR returns the position of the first occurrence of the substring, starting at 1.

The first 'a' in 'database' is at position 2.

📝 Syntax

advanced

Identify the syntax error in LOCATE usage

Which option contains a syntax error when using LOCATE in MySQL?

A SELECT LOCATE('test');

B SELECT LOCATE('test', 'testing', 2);

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

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

💡 Hint

LOCATE requires at least two arguments: substring and string.

Option A is missing the second argument (the string to search in), causing a syntax error.

❓ query_result

advanced

LOCATE with start position parameter

What is the output of this query?

SELECT LOCATE('a', 'banana', 3);

MySQL

SELECT LOCATE('a', 'banana', 3);

A 4

B 2

C 3

D 0

💡 Hint

The search starts at position 3 in the string.

Starting from the 3rd character, the first 'a' appears at position 4.

🧠 Conceptual

expert

Behavior of INSTR when substring is not found

What is the output of this query?

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

MySQL

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

A 1

B NULL

C -1

D 0

💡 Hint

INSTR returns 0 if the substring is not found.

Since 'x' is not in 'hello world', INSTR returns 0.

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