REPLACE function in MySQL - Step-by-Step Execution

Concept Flow - REPLACE function

Input String

↓

Search for substring

Yes↓

Replace substring with new text

↓

Return new string

No↓

Return original string

The REPLACE function looks for a substring in a string and replaces all its occurrences with new text, returning the updated string.

Execution Sample

MySQL

SELECT REPLACE('hello world', 'world', 'MySQL');

This query replaces 'world' with 'MySQL' in the string 'hello world'.

Execution Table

Step	Input String	Search Substring	Replace With	Action	Result
1	'hello world'	'world'	'MySQL'	Search for 'world' in 'hello world'	Found at position 7
2	'hello world'	'world'	'MySQL'	Replace 'world' with 'MySQL'	'hello MySQL'
3	'hello MySQL'	'world'	'MySQL'	No more 'world' found	'hello MySQL'

💡 No more occurrences of 'world' found, replacement complete.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Input String	'hello world'	'hello world'	'hello MySQL'	'hello MySQL'
Result String	''	''	'hello MySQL'	'hello MySQL'

Key Moments - 2 Insights

Why does REPLACE return the original string if the search substring is not found?

Because as shown in execution_table row 3, when the search substring is not found, REPLACE does no changes and returns the original string.

Does REPLACE change only the first occurrence or all occurrences?

REPLACE changes all occurrences of the search substring, but in this example only one occurrence exists, so only one replacement happens (see execution_table row 2).

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after step 2?

A 'hello world'

B 'MySQL world'

C 'hello MySQL'

D 'hello'

💡 Hint

Check the 'Result' column in execution_table row 2.

At which step does REPLACE find no more occurrences of the search substring?

A Step 1

B Step 3

C Step 2

D No step

💡 Hint

Look at the 'Action' column in execution_table row 3.

If the input string was 'world world', how many replacements would REPLACE perform?

A Two

B One

C Zero

D Three

💡 Hint

REPLACE replaces all occurrences of the search substring.

Concept Snapshot

REPLACE(str, from_substr, to_substr)
- Searches str for all occurrences of from_substr
- Replaces each with to_substr
- Returns the new string
- If from_substr not found, returns original string
- Replaces all occurrences, not just first

Full Transcript

The REPLACE function in MySQL takes three inputs: the original string, the substring to find, and the substring to replace it with. It searches the original string for all occurrences of the search substring. If found, it replaces each occurrence with the new substring. If the search substring is not found, it returns the original string unchanged. For example, replacing 'world' with 'MySQL' in 'hello world' results in 'hello MySQL'. This process continues until no more occurrences are found. REPLACE always replaces all occurrences, not just the first one.