REPLACE function in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the REPLACE function do in SQL?

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

beginner

Syntax of the REPLACE function in SQL?

REPLACE(original_string, string_to_find, string_to_replace_with)

beginner

Example: What is the result of REPLACE('apple pie', 'pie', 'tart')?

The result is 'apple tart' because 'pie' is replaced by 'tart'.

intermediate

Can REPLACE function change multiple occurrences in a string?

Yes, REPLACE changes all occurrences of the target substring in the original string.

beginner

What happens if the string to find is not present in the original string?

The original string is returned unchanged because there is nothing to replace.

What does REPLACE('banana', 'a', 'o') return?

A bonono

B banana

C bnn

D banano

All 'a' characters are replaced with 'o', so 'banana' becomes 'bonono'.

Which of these is the correct syntax for REPLACE?

A REPLACE(string_to_find, original_string, replacement)

B REPLACE(original_string, string_to_find, replacement)

C REPLACE(replacement, original_string, string_to_find)

D REPLACE(original_string, replacement, string_to_find)

The correct order is original string, string to find, then replacement string.

If REPLACE('hello world', 'x', 'y') is run, what is the output?

A error

B hello yorld

C hello worly

D hello world

Since 'x' is not found, the original string is returned unchanged.

Does REPLACE function modify the original data in the database?

A No, it only returns a modified string without changing stored data

B Yes, it updates the stored data automatically

C Yes, but only if used with UPDATE statement

D No, it deletes the original data

REPLACE returns a new string with replacements; it does not change stored data unless used inside an UPDATE.

Which SQL function would you use to replace text inside a string?

A SUBSTRING

B CONCAT

C REPLACE

D TRIM

REPLACE is used to substitute parts of a string with new text.

Explain how the REPLACE function works in SQL with an example.

Think about how you would change words in a sentence.

What happens if the substring to replace is not found in the original string when using REPLACE?

Consider what happens if you try to replace a word that isn't there.