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.