REPLACE function in SQL - Step-by-Step Execution

Concept Flow - REPLACE function

Input String

↓

Search for substring

↓

If found?

No→Return original string

Yes↓

Replace substring with new substring

↓

Return modified string

The REPLACE function takes a string, finds all occurrences of a substring, replaces them with another substring, and returns the new string.

Execution Sample

SQL

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

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

Execution Table

Step	Input String	Substring to Find	Substring to Replace	Action	Output
1	'hello world'	'world'	'SQL'	Search for 'world' in 'hello world'	'hello world' contains 'world'
2	'hello world'	'world'	'SQL'	Replace 'world' with 'SQL'	'hello SQL'
3	'hello SQL'	'world'	'SQL'	Return the modified string	'hello SQL'

💡 All occurrences of 'world' replaced; function returns the new string.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Input String	'hello world'	'hello world'	'hello SQL'	'hello SQL'
Substring to Find	'world'	'world'	'world'	'world'
Substring to Replace	'SQL'	'SQL'	'SQL'	'SQL'
Output	NULL	NULL	'hello SQL'	'hello SQL'

Key Moments - 2 Insights

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

If the substring is not found, the REPLACE function returns the original string unchanged.

Does REPLACE change only the first occurrence or all occurrences of the substring?

REPLACE changes all occurrences of the substring in the input string, not just the first one. This is implied by the function's behavior and the exit note.

Visual Quiz - 3 Questions

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

A 'SQL world'

B 'hello world'

C 'hello SQL'

D 'hello'

💡 Hint

Check the 'Output' column in row for step 2 in execution_table.

At which step does the function confirm the substring exists in the input string?

A Step 2

B Step 1

C Step 3

D No step confirms substring presence

💡 Hint

Look at the 'Action' column in execution_table step 1.

If the input string was 'hello world world', how would the output change?

A 'hello SQL SQL'

B 'hello world SQL'

C 'hello SQL world'

D 'hello world world'

💡 Hint

REPLACE changes all occurrences; see key_moments about replacing all occurrences.

Concept Snapshot

REPLACE(string, from_substring, to_substring)
- Finds all occurrences of from_substring in string
- Replaces them with to_substring
- Returns the new string
- If from_substring not found, returns original string
- Works on all occurrences, not just first

Full Transcript

The REPLACE function in SQL 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 substring to find. If it finds any, it replaces all of them with the new substring. If it does not find the substring, it returns the original string unchanged. For example, replacing 'world' with 'SQL' in 'hello world' results in 'hello SQL'. This function is useful for changing parts of text data in a database.