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

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

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;

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');

Hint

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

Practice

1. What does the REPLACE function do in MySQL?

easy

A. It deletes the entire string if a substring is found.

B. It splits the string into multiple parts.

C. It adds a substring at the end of the string.

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

Solution

1. Step 1: Understand the purpose of REPLACE

   The REPLACE function searches for all occurrences of a substring inside a string and replaces them with another substring.

2. Step 2: Compare options with the function behavior

   Only It replaces all occurrences of a specified substring within a string. correctly describes this behavior. Options A, C, and D describe different string operations.

3. Final Answer:

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

4. Quick Check:

   REPLACE changes parts of text inside strings = B [OK]

Hint: REPLACE swaps all found text parts inside a string [OK]

Common Mistakes:

• Thinking REPLACE deletes the whole string
• Confusing REPLACE with string splitting
• Assuming REPLACE adds text instead of replacing

2. Which of the following is the correct syntax to replace 'cat' with 'dog' in the string 'catapult' using MySQL?

easy

A. REPLACE('catapult', 'cat', 'dog')

B. REPLACE('catapult', 'dog', 'cat')

C. REPLACE('catapult', 'catapult', 'dog')

D. REPLACE('catapult', 'dog', 'dog')

Solution

1. Step 1: Recall REPLACE syntax

   The syntax is REPLACE(original_string, string_to_find, string_to_replace_with).

2. Step 2: Match parameters to the question

   We want to replace 'cat' with 'dog' in 'catapult', so the correct call is REPLACE('catapult', 'cat', 'dog').

3. Final Answer:

   REPLACE('catapult', 'cat', 'dog') -> Option A

4. Quick Check:

   REPLACE(original, find, replace) = A [OK]

Hint: Syntax is REPLACE(text, find, replace) always [OK]

Common Mistakes:

• Swapping find and replace arguments
• Replacing the whole string instead of substring
• Using wrong order of parameters

3. What is the result of this query?

SELECT REPLACE('hello world', 'l', 'x');

medium

A. 'hello world'

B. 'hexxo worlx'

C. 'hexxo worxd'

D. 'hexxo worll'

Solution

1. Step 1: Identify what is replaced

   All occurrences of 'l' in 'hello world' are replaced with 'x'.

2. Step 2: Perform the replacement

   'hello world' becomes 'hexxo worlx' because every 'l' changes to 'x'.

3. Final Answer:

   'hexxo worlx' -> Option B

4. Quick Check:

   Replace all 'l' with 'x' = 'hexxo worlx' [OK]

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

Common Mistakes:

• Replacing only first occurrence
• Not replacing at all
• Replacing wrong characters

4. Identify the error in this query:

SELECT REPLACE('database', 'a');

medium

A. Missing third argument for replacement string.

B. The first argument should be a column name, not a string.

C. REPLACE cannot be used in SELECT statements.

D. The function name should be REPLACESTR.

Solution

1. Step 1: Check REPLACE function arguments

   REPLACE requires three arguments: original string, string to find, and string to replace with.

2. Step 2: Identify missing argument

   The query only has two arguments, missing the replacement string.

3. Final Answer:

   Missing third argument for replacement string. -> Option A

4. Quick Check:

   REPLACE needs 3 arguments = A [OK]

Hint: REPLACE needs 3 arguments: original, find, replace [OK]

Common Mistakes:

• Forgetting the replacement string
• Using wrong function name
• Thinking REPLACE can't be in SELECT

5. Given a table products with a column code containing values like 'AB-123', 'AB-456', and 'CD-789', which query replaces the prefix 'AB-' with 'XY-' only in the code column?

hard

A. SELECT REPLACE(code, 'AB', 'XY') AS new_code FROM products;

B. SELECT REPLACE('code', 'AB-', 'XY-') AS new_code FROM products;

C. SELECT REPLACE(code, 'AB-', 'XY-') AS new_code FROM products;

D. SELECT REPLACE(code, 'XY-', 'AB-') AS new_code FROM products;

Solution

1. Step 1: Understand the goal

   We want to replace the prefix 'AB-' with 'XY-' in the column code values.

2. Step 2: Analyze each option

   SELECT REPLACE(code, 'AB-', 'XY-') AS new_code FROM products; correctly uses REPLACE on the column code replacing 'AB-' with 'XY-'. SELECT REPLACE('code', 'AB-', 'XY-') AS new_code FROM products; treats 'code' as a string literal, not a column. SELECT REPLACE(code, 'XY-', 'AB-') AS new_code FROM products; reverses replacement. SELECT REPLACE(code, 'AB', 'XY') AS new_code FROM products; replaces 'AB' without the dash, which may affect unintended parts.

3. Final Answer:

   SELECT REPLACE(code, 'AB-', 'XY-') AS new_code FROM products; -> Option C

4. Quick Check:

   Replace prefix 'AB-' with 'XY-' in column = D [OK]

Hint: Use column name as first argument, exact substring to replace [OK]

Common Mistakes:

• Using quotes around column name
• Replacing wrong substring
• Swapping find and replace strings