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

Need a 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

Need a 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

Need a 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

Need a hint?

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