REPLACE function in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this REPLACE function query?

Consider the following SQL query:

SELECT REPLACE('apple banana apple', 'apple', 'orange') AS result;

What will be the value of result?

MySQL

SELECT REPLACE('apple banana apple', 'apple', 'orange') AS result;

A 'orange banana apple'

B 'apple banana orange'

C 'apple banana apple'

D 'orange banana orange'

💡 Hint

REPLACE substitutes all occurrences of the search string with the replacement string.

The REPLACE function replaces every occurrence of 'apple' with 'orange' in the string, so both 'apple's become 'orange'.

❓ query_result

intermediate

What does this REPLACE query return?

Given the query:

SELECT REPLACE('123-456-7890', '-', '') AS phone;

What is the value of phone?

MySQL

SELECT REPLACE('123-456-7890', '-', '') AS phone;

A '123 456 7890'

B '123-456-7890'

C '1234567890'

D '123--456--7890'

💡 Hint

REPLACE removes all dashes by replacing them with an empty string.

Replacing '-' with '' removes all dashes, so the phone number becomes a continuous string of digits.

📝 Syntax

advanced

Which REPLACE function usage is syntactically correct?

Identify the correct syntax for the REPLACE function in MySQL:

A REPLACE('hello world', 'world')

B REPLACE('hello world', 'world', 'everyone')

C REPLACE('hello world', 'world', 'everyone', 'extra')

D REPLACE('hello world' 'world' 'everyone')

💡 Hint

REPLACE requires exactly three arguments: the original string, the substring to find, and the substring to replace it with.

Option B correctly uses three arguments. Option B misses the replacement string, B has too many arguments, and D lacks commas.

❓ optimization

advanced

Optimizing multiple REPLACE calls in a query

You want to replace all commas and semicolons in a string with spaces. Which query is more efficient?

A SELECT REPLACE(REPLACE(column_name, ',', ' '), ';', ' ') FROM table_name;

B SELECT REPLACE(column_name, ',;', ' ') FROM table_name;

C SELECT REPLACE(column_name, ',', ' ') FROM table_name WHERE column_name LIKE '%;';

D SELECT REPLACE(column_name, ';', ' ') FROM table_name WHERE column_name LIKE '%,%';

💡 Hint

REPLACE only replaces exact substrings, so multiple nested calls may be needed for different characters.

Option A correctly replaces commas and semicolons by nesting REPLACE calls. Option A tries to replace ',;' as a single substring which won't work. Options A and C only replace one character conditionally.

🧠 Conceptual

expert

What happens if the search string is empty in REPLACE?

Consider this query:

SELECT REPLACE('database', '', 'X') AS result;

What will be the output?

MySQL

SELECT REPLACE('database', '', 'X') AS result;

A 'XdXaXtXaXbXaXsXeX'

B NULL

C Syntax error

D 'database'

💡 Hint

Replacing an empty string inserts the replacement string between every character and at the start and end.

When the search string is empty, REPLACE inserts the replacement string between every character and at the start and end of the original string.