REPLACE function in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the REPLACE function do in MySQL?

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

beginner

Syntax of the REPLACE function in MySQL?

REPLACE(original_string, from_substring, to_substring) - It returns a new string where all occurrences of from_substring are replaced by to_substring.

beginner

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

The REPLACE function returns the original string unchanged if the substring to replace is not found.

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 replace multiple occurrences of the substring?

Yes, REPLACE replaces all occurrences of the substring in the original string, not just the first one.

What does REPLACE('hello world', 'world', 'there') return?

A 'there world'

B 'hello world'

C 'hello there'

D 'hello'

REPLACE changes 'world' to 'there', so the result is 'hello there'.

If the substring to replace is not found, what does REPLACE return?

A Original string unchanged

B Empty string

C NULL

D An error

REPLACE returns the original string unchanged if the substring is not found.

Which of these is the correct syntax for REPLACE?

A REPLACE(string, old_substring, new_substring)

B REPLACE(old_substring, new_substring, string)

C REPLACE(string, new_substring, old_substring)

D REPLACE(new_substring, string, old_substring)

The correct syntax is REPLACE(original_string, from_substring, to_substring).

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

A banana

B bano

C bnn

D bonono

All 'a's are replaced by 'o's, so 'banana' becomes 'bonono'.

Can REPLACE be used to remove a substring by replacing it with an empty string?

A Only if substring is at the start

B Yes

C No

D Only if substring is at the end

Replacing a substring with an empty string effectively removes it.

Explain how the REPLACE function works in MySQL and give a simple example.

Think about changing parts of a word to another word.

Describe what happens if the substring to replace does not exist in the original string when using REPLACE.

What if you try to replace something that isn't there?