REPLACE function in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this REPLACE function query?

Given the string 'hello world', what will be the result of this SQL query?

SELECT REPLACE('hello world', 'world', 'SQL') AS result;

SQL

SELECT REPLACE('hello world', 'world', 'SQL') AS result;

A 'hello SQL'

B 'hello world'

C 'SQL world'

D 'helloSQL'

💡 Hint

REPLACE changes all occurrences of the second string with the third string inside the first string.

The REPLACE function looks for 'world' in 'hello world' and replaces it with 'SQL', resulting in 'hello SQL'.

❓ query_result

intermediate

What does this REPLACE query return?

What is the output of this SQL query?

SELECT REPLACE('banana', 'a', 'o') AS result;

SQL

SELECT REPLACE('banana', 'a', 'o') AS result;

A 'bnn'

B 'banana'

C 'bonono'

D 'banano'

💡 Hint

Every 'a' in 'banana' is replaced by 'o'.

The REPLACE function replaces all 'a' characters with 'o', so 'banana' becomes 'bonono'.

📝 Syntax

advanced

Which query will cause a syntax error?

Identify which of these SQL queries using REPLACE will cause a syntax error.

A SELECT REPLACE('test', 't', 'T') AS result;

B SELECT REPLACE('test', 't') AS result;

C SELECT REPLACE('test', 't', 'T', 'extra') AS result;

D ;tluser SA )'T' ,'t' ,'tset'(ECALPER TCELES

💡 Hint

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

Option B has only two arguments, which is invalid syntax for REPLACE. Options A and B are correct syntax. Option B has too many arguments and will also cause an error, but the question asks for one correct answer causing syntax error, and D is the invalid with missing argument.

🔧 Debug

advanced

Why does this REPLACE query return the original string?

Consider this query:

SELECT REPLACE('apple', 'b', 'c') AS result;

Why does it return 'apple' unchanged?

SQL

SELECT REPLACE('apple', 'b', 'c') AS result;

A Because the function is case-sensitive and 'B' is different from 'b'.

B Because REPLACE only works with numeric strings.

C Because the third argument must be longer than the second.

D Because 'b' is not found in 'apple', so no replacement happens.

💡 Hint

Think about what happens if the substring to replace is not found.

If the substring to replace is not found in the original string, REPLACE returns the original string unchanged.

🧠 Conceptual

expert

How many replacements occur in this query?

Given the string 'abracadabra', how many characters will be replaced by this query?

SELECT REPLACE('abracadabra', 'a', 'x') AS result;

SQL

SELECT REPLACE('abracadabra', 'a', 'x') AS result;

A 5

B 4

C 3

D 6

💡 Hint

Count how many times 'a' appears in 'abracadabra'.

'abracadabra' contains 5 'a' characters, so 5 replacements occur.