REPLACE function in MySQL - Mini Project: Build & Apply

Using the REPLACE Function in MySQL

📖 Scenario: You work for a bookstore that keeps a list of book titles in a database. Some titles have a typo that needs fixing.

🎯 Goal: You will create a table with book titles, set a target word to replace, use the REPLACE function to fix typos in the titles, and finally update the table with corrected titles.

📋 What You'll Learn

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

Insert three rows with specific book titles containing the word 'Harrpotter' (typo).

Create a variable or use a string literal for the incorrect word 'Harrpotter'.

Use the REPLACE function to replace 'Harrpotter' with 'Harry Potter' in the titles.

Update the books table with the corrected titles.

💡 Why This Matters

🌍 Real World

Fixing typos or unwanted text in database records is common in data cleaning and maintenance tasks.

💼 Career

Database administrators and developers often use the REPLACE function to correct data without manual edits.

1

Create the books table and insert data

Create a table called books with columns id as integer and title as text. Insert these three rows exactly: (1, 'Harrpotter and the Sorcerer''s Stone'), (2, 'Harrpotter and the Chamber of Secrets'), (3, 'Harrpotter and the Prisoner of Azkaban').

MySQL

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

Need a hint?

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

2

Set the incorrect word to replace

Create a variable or use a string literal called incorrect_word and set it to 'Harrpotter' to represent the typo you want to fix.

MySQL

CREATE TABLE books (id INT, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Harrpotter and the Sorcerer''s Stone'),
(2, 'Harrpotter and the Chamber of Secrets'),
(3, 'Harrpotter and the Prisoner of Azkaban');
-- Set the incorrect word to replace
SET @incorrect_word = 'Harrpotter';

Need a hint?

Use SET @incorrect_word = 'Harrpotter'; to create a variable for the typo.

3

Use REPLACE to fix the titles

Write a SELECT query that shows the id and the title with the typo replaced by 'Harry Potter' using the REPLACE function. Use the variable @incorrect_word as the string to replace.

MySQL

CREATE TABLE books (id INT, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Harrpotter and the Sorcerer''s Stone'),
(2, 'Harrpotter and the Chamber of Secrets'),
(3, 'Harrpotter and the Prisoner of Azkaban');

SET @incorrect_word = 'Harrpotter';

-- Select id and fixed title using REPLACE
SELECT id, REPLACE(title, @incorrect_word, 'Harry Potter') AS fixed_title FROM books;

Need a hint?

Use REPLACE(title, @incorrect_word, 'Harry Potter') inside the SELECT statement.

4

Update the books table with corrected titles

Write an UPDATE statement that replaces the typo @incorrect_word with 'Harry Potter' in the title column of the books table using the REPLACE function.

MySQL

CREATE TABLE books (id INT, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Harrpotter and the Sorcerer''s Stone'),
(2, 'Harrpotter and the Chamber of Secrets'),
(3, 'Harrpotter and the Prisoner of Azkaban');

SET @incorrect_word = 'Harrpotter';

SELECT id, REPLACE(title, @incorrect_word, 'Harry Potter') AS fixed_title FROM books;

-- Update the books table to fix the titles
UPDATE books SET title = REPLACE(title, @incorrect_word, 'Harry Potter');

Need a hint?

Use UPDATE books SET title = REPLACE(title, @incorrect_word, 'Harry Potter'); to fix the titles in the table.