LOCATE and INSTR in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LOCATE function do in MySQL?

LOCATE finds the position of a substring inside a string and returns the position as a number. If the substring is not found, it returns 0.

intermediate

How is INSTR different from LOCATE in MySQL?

INSTR also finds the position of a substring inside a string but does not accept a starting position argument. LOCATE can start searching from a specific position, INSTR always searches from the beginning.

beginner

Write the syntax of LOCATE function.

LOCATE(substring, string, start_position) - start_position is optional and tells where to start searching.

beginner

What will LOCATE('cat', 'concatenate') return?

It returns 4 because 'cat' starts at the 4th character in 'concatenate'.

beginner

If INSTR('hello world', 'o') is used, what is the output?

It returns 5 because the first 'o' appears at the 5th position in 'hello world'.

What does LOCATE('a', 'banana') return?

A 0

B 1

C 3

D 2

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

Which function allows specifying a start position for searching a substring?

A LOCATE

B SUBSTRING

C INSTR

D LENGTH

LOCATE lets you specify where to start searching; INSTR does not.

What does INSTR('apple', 'p') return?

A 3

B 2

C 1

D 0

The first 'p' in 'apple' is at position 2.

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

A 0

B -1

C NULL

D 1

Both return 0 when the substring is not found.

Which of these is a valid LOCATE usage?

A LOCATE('text', 'x')

B LOCATE('x', 'text', '3')

C LOCATE('x', 'text', 3)

D LOCATE('x')

LOCATE requires at least substring and string; start position is optional and must be a number.

Explain how LOCATE and INSTR functions work in MySQL and when you might use each.

Think about searching inside words and starting points.

Describe the difference in syntax and behavior between LOCATE and INSTR.

Focus on argument order and optional parameters.