LOCATE and INSTR in MySQL - Mini Project: Build & Apply

Finding Substrings with LOCATE and INSTR in MySQL

📖 Scenario: You work at a small bookstore. You have a table of book titles, and you want to find where certain words appear inside those titles.

🎯 Goal: Build a simple MySQL query that uses LOCATE and INSTR functions to find the position of a word inside book titles.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (varchar).

Insert exactly these three rows into books: (1, 'The Great Gatsby'), (2, 'Great Expectations'), (3, 'The Grapes of Wrath').

Create a variable called search_word and set it to the string 'Great'.

Write a SELECT query that uses LOCATE(search_word, title) to find the position of search_word in each title.

Write a SELECT query that uses INSTR(title, search_word) to find the position of search_word in each title.

💡 Why This Matters

🌍 Real World

Finding where a word or phrase appears inside text fields is common in searching, filtering, and data cleaning tasks.

💼 Career

Database developers and analysts often use LOCATE and INSTR to write queries that find and manipulate text data efficiently.

1

Create the books table and insert data

Create a table called books with columns id as INT and title as VARCHAR(100). Then insert these exact rows: (1, 'The Great Gatsby'), (2, 'Great Expectations'), and (3, 'The Grapes of Wrath').

MySQL

-- Create the books table and insert the three rows
-- Your code here

Hint

Use CREATE TABLE to make the table, then INSERT INTO to add rows.

2

Set the search_word variable

Create a variable called search_word and set it to the string 'Great'. Use SET @search_word = 'Great'; to do this.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100)
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Great Expectations'),
(3, 'The Grapes of Wrath');

-- Set the search_word variable to 'Great'
-- Your code here

Hint

Use SET @search_word = 'Great'; to create the variable.

3

Use LOCATE to find the word position

Write a SELECT query that shows id, title, and the position of @search_word inside title using LOCATE(@search_word, title). Name the position column locate_pos.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100)
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Great Expectations'),
(3, 'The Grapes of Wrath');

SET @search_word = 'Great';

-- Write a SELECT query using LOCATE(@search_word, title) as locate_pos
-- Your code here

Hint

Use LOCATE(@search_word, title) inside the SELECT statement.

4

Use INSTR to find the word position

Write a SELECT query that shows id, title, and the position of @search_word inside title using INSTR(title, @search_word). Name the position column instr_pos.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100)
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Great Expectations'),
(3, 'The Grapes of Wrath');

SET @search_word = 'Great';

SELECT id, title, LOCATE(@search_word, title) AS locate_pos FROM books;

-- Write a SELECT query using INSTR(title, @search_word) as instr_pos
-- Your code here

Hint

Use INSTR(title, @search_word) inside the SELECT statement.

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