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

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

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

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

Hint

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

Practice

1. What does the SQL REPLACE function do?

easy

A. It converts all characters in a string to uppercase.

B. It deletes a substring from a string without replacement.

C. It extracts a substring from a string based on position.

D. It replaces all occurrences of a specified substring within a string with another substring.

Solution

1. Step 1: Understand the purpose of REPLACE

   The REPLACE function is designed to find all instances of a substring in a string and swap them with another substring.

2. Step 2: Compare with other string functions

   Unlike deleting or extracting substrings, REPLACE changes the content by substitution.

3. Final Answer:

   It replaces all occurrences of a specified substring within a string with another substring. -> Option D

4. Quick Check:

   REPLACE swaps substrings = A [OK]

Hint: REPLACE swaps all target text with new text [OK]

Common Mistakes:

• Thinking REPLACE deletes text without substitution
• Confusing REPLACE with substring extraction
• Assuming REPLACE changes case of text

2. Which of the following is the correct syntax for the SQL REPLACE function?

easy

A. REPLACE(string, old_substring, new_substring)

B. REPLACE(old_substring, new_substring, string)

C. REPLACE(string, new_substring, old_substring)

D. REPLACE(new_substring, string, old_substring)

Solution

1. Step 1: Recall REPLACE function parameter order

   The correct order is the original string first, then the substring to replace, then the new substring.

2. Step 2: Verify each option

   Only REPLACE(string, old_substring, new_substring) follows the correct order: REPLACE(string, old_substring, new_substring).

3. Final Answer:

   REPLACE(string, old_substring, new_substring) -> Option A

4. Quick Check:

   Correct parameter order = D [OK]

Hint: Remember: original string first, then old, then new substring [OK]

Common Mistakes:

• Swapping old and new substring positions
• Putting new substring before old substring
• Confusing parameter order

3. Given the SQL query:
SELECT REPLACE('banana', 'a', 'o');
What is the output?

medium

A. 'bnn'

B. 'banana'

C. 'bonono'

D. 'banano'

Solution

1. Step 1: Identify the string and substrings

   The original string is 'banana'. We replace all 'a' with 'o'.

2. Step 2: Replace all 'a' with 'o'

   Replacing each 'a' in 'banana' results in 'bonono'.

3. Final Answer:

   'bonono' -> Option C

4. Quick Check:

   All 'a' replaced by 'o' = 'bonono' [OK]

Hint: Replace every target letter, not just first [OK]

Common Mistakes:

• Replacing only first occurrence
• Not replacing any character
• Removing characters instead of replacing

4. What is wrong with this SQL query?
SELECT REPLACE('hello world', 'l');

medium

A. It is missing the new substring to replace with.

B. The string to search in is missing.

C. The REPLACE function cannot be used in SELECT statements.

D. The function should be written as REPLACE('l', 'hello world').

Solution

1. Step 1: Check REPLACE function parameters

   REPLACE requires three parameters: original string, old substring, new substring.

2. Step 2: Identify missing parameter

   The query only has two parameters: original string and old substring. The new substring is missing.

3. Final Answer:

   It is missing the new substring to replace with. -> Option A

4. Quick Check:

   Missing third parameter = A [OK]

Hint: REPLACE needs 3 parameters: string, old, new [OK]

Common Mistakes:

• Using only two parameters
• Swapping parameter order
• Assuming REPLACE works with one substring only

5. You have a table products with a column description containing the text 'color' spelled as 'colour' in some rows. Which SQL query correctly updates all 'colour' to 'color' in the description column?

hard

A. UPDATE products SET description = REPLACE('colour', 'color', description);

B. UPDATE products SET description = REPLACE(description, 'colour', 'color');

C. UPDATE products SET description = REPLACE(description, 'color', 'colour');

D. UPDATE products SET description = REPLACE(description, 'colour');

Solution

1. Step 1: Understand the goal

   We want to replace all occurrences of 'colour' with 'color' in the description column.

2. Step 2: Check each query's correctness

   UPDATE products SET description = REPLACE(description, 'colour', 'color'); correctly uses REPLACE with the column first, then old substring 'colour', then new substring 'color'. UPDATE products SET description = REPLACE('colour', 'color', description); has wrong parameter order. UPDATE products SET description = REPLACE(description, 'color', 'colour'); replaces 'color' with 'colour' (opposite). UPDATE products SET description = REPLACE(description, 'colour'); misses the new substring parameter.

3. Final Answer:

   UPDATE products SET description = REPLACE(description, 'colour', 'color'); -> Option B

4. Quick Check:

   Correct update syntax with REPLACE = B [OK]

Hint: Use REPLACE(column, old, new) in UPDATE to fix text [OK]

Common Mistakes:

• Swapping old and new substrings
• Missing new substring parameter
• Using wrong parameter order