REPLACE function in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to replace all occurrences of 'cat' with 'dog' in the column 'pet' from the table 'animals'.

MySQL

SELECT REPLACE(pet, [1], 'dog') FROM animals;

Drag options to blanks, or click blank then click option'

A'cat'

B'dog'

C'pet'

D'animal'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong string to replace, like 'dog' instead of 'cat'.

Confusing the order of arguments in REPLACE.

The REPLACE function replaces all occurrences of the first string ('cat') with the second string ('dog') in the given column.

2fill in blank

medium

Complete the code to replace all spaces with underscores in the 'username' column of the 'users' table.

MySQL

SELECT REPLACE(username, [1], '_') FROM users;

Drag options to blanks, or click blank then click option'

A' '

B'_'

C'-'

D'space'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '_' as the string to find instead of the replacement.

Using the word 'space' instead of the space character.

To replace spaces, you specify a space character (' ') as the string to find in REPLACE.

3fill in blank

hard

Fix the error in the code to replace 'old' with 'new' in the 'description' column from 'products'.

MySQL

SELECT REPLACE(description, [1], 'new') FROM products;

Drag options to blanks, or click blank then click option'

Aold

Bdescription

C"old"

D'old'

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around string literals.

Using double quotes which may not work in MySQL for strings.

String literals in SQL must be enclosed in single quotes, so 'old' is correct.

4fill in blank

hard

Fill both blanks to replace 'blue' with 'red' in the 'color' column from 'items' table.

MySQL

SELECT REPLACE(color, [1], [2]) FROM items;

Drag options to blanks, or click blank then click option'

A'blue'

B'red'

C'green'

D'yellow'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of the strings.

Using wrong strings that don't match the task.

The first argument is the string to find ('blue'), the second is the replacement ('red').

5fill in blank

hard

Fill all three blanks to replace 'cat' with 'dog' in the 'pet' column and rename the output column as 'new_pet' in the 'animals' table.

MySQL

SELECT REPLACE([1], [2], [3]) AS new_pet FROM animals;

Drag options to blanks, or click blank then click option'

Apet

B'cat'

C'dog'

Danimals

Attempts:

3 left

💡 Hint

Common Mistakes

Using table name instead of column name.

Not quoting the strings to find and replace.

The first blank is the column name 'pet', the second is the string to find 'cat', the third is the replacement 'dog'.

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