REPLACE function in SQL - Mini Project: Build & Apply

Using the REPLACE Function in SQL

📖 Scenario: You work for a bookstore that stores book titles in a database. Some titles have the word 'Edition' spelled as 'Edtion' by mistake. You want to fix these titles using SQL.

🎯 Goal: Learn how to use the SQL REPLACE function to correct misspelled words in book titles.

📋 What You'll Learn

Create a table called books with columns id and title.

Insert three book titles into the books table, including one with the misspelled word 'Edtion'.

Write a query that uses the REPLACE function to fix the misspelled word in the titles.

Select the corrected titles from the table.

💡 Why This Matters

🌍 Real World

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

💼 Career

Database administrators and data analysts often use REPLACE to correct data errors without manual editing.

1

Create the books table and insert data

Create a table called books with columns id (integer) and title (text). Insert these three rows exactly: (1, 'Learn SQL Edtion'), (2, 'Mastering Python'), (3, 'Data Science Edtion').

SQL

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

Add a query to fix the misspelled word

Write a SELECT query that uses the REPLACE function to replace the misspelled word 'Edtion' with 'Edition' in the title column. Select the corrected titles as corrected_title.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Learn SQL Edtion'),
(2, 'Mastering Python'),
(3, 'Data Science Edtion');

-- Write a SELECT query using REPLACE to fix 'Edtion' to 'Edition'
-- Your code here

Need a hint?

Use REPLACE(column, 'old', 'new') inside the SELECT statement.

3

Add a WHERE clause to show only titles with the misspelled word

Modify the SELECT query to include a WHERE clause that filters rows where the title contains the misspelled word 'Edtion'.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Learn SQL Edtion'),
(2, 'Mastering Python'),
(3, 'Data Science Edtion');

-- Add a WHERE clause to select only titles containing 'Edtion'
-- Your code here

Need a hint?

Use WHERE title LIKE '%Edtion%' to filter titles containing the misspelled word.

4

Update the table to fix the misspelled word permanently

Write an UPDATE statement that uses the REPLACE function to permanently fix the misspelled word 'Edtion' to 'Edition' in the title column for all rows where the misspelled word appears.

SQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Learn SQL Edtion'),
(2, 'Mastering Python'),
(3, 'Data Science Edtion');

-- Write an UPDATE statement to fix 'Edtion' to 'Edition' permanently
-- Your code here

Need a hint?

Use UPDATE with SET column = REPLACE(column, 'old', 'new') and a WHERE clause.