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.

Practice

1. What does the SQL REPLACE function do?

easy

A. It converts all characters in a string to uppercase.

B. It deletes a substring from a string without replacement.

C. It extracts a substring from a string based on position.

D. It replaces all occurrences of a specified substring within a string with another substring.

Solution

1. Step 1: Understand the purpose of REPLACE

   The REPLACE function is designed to find all instances of a substring in a string and swap them with another substring.

2. Step 2: Compare with other string functions

   Unlike deleting or extracting substrings, REPLACE changes the content by substitution.

3. Final Answer:

   It replaces all occurrences of a specified substring within a string with another substring. -> Option D

4. Quick Check:

   REPLACE swaps substrings = A [OK]

Hint: REPLACE swaps all target text with new text [OK]

Common Mistakes:

• Thinking REPLACE deletes text without substitution
• Confusing REPLACE with substring extraction
• Assuming REPLACE changes case of text

2. Which of the following is the correct syntax for the SQL REPLACE function?

easy

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)

Solution

1. Step 1: Recall REPLACE function parameter order

   The correct order is the original string first, then the substring to replace, then the new substring.

2. Step 2: Verify each option

   Only REPLACE(string, old_substring, new_substring) follows the correct order: REPLACE(string, old_substring, new_substring).

3. Final Answer:

   REPLACE(string, old_substring, new_substring) -> Option A

4. Quick Check:

   Correct parameter order = D [OK]

Hint: Remember: original string first, then old, then new substring [OK]

Common Mistakes:

• Swapping old and new substring positions
• Putting new substring before old substring
• Confusing parameter order

3. Given the SQL query:
SELECT REPLACE('banana', 'a', 'o');
What is the output?

medium

A. 'bnn'

B. 'banana'

C. 'bonono'

D. 'banano'

Solution

1. Step 1: Identify the string and substrings

   The original string is 'banana'. We replace all 'a' with 'o'.

2. Step 2: Replace all 'a' with 'o'

   Replacing each 'a' in 'banana' results in 'bonono'.

3. Final Answer:

   'bonono' -> Option C

4. Quick Check:

   All 'a' replaced by 'o' = 'bonono' [OK]

Hint: Replace every target letter, not just first [OK]

Common Mistakes:

• Replacing only first occurrence
• Not replacing any character
• Removing characters instead of replacing

4. What is wrong with this SQL query?
SELECT REPLACE('hello world', 'l');

medium

A. It is missing the new substring to replace with.

B. The string to search in is missing.

C. The REPLACE function cannot be used in SELECT statements.

D. The function should be written as REPLACE('l', 'hello world').

Solution

1. Step 1: Check REPLACE function parameters

   REPLACE requires three parameters: original string, old substring, new substring.

2. Step 2: Identify missing parameter

   The query only has two parameters: original string and old substring. The new substring is missing.

3. Final Answer:

   It is missing the new substring to replace with. -> Option A

4. Quick Check:

   Missing third parameter = A [OK]

Hint: REPLACE needs 3 parameters: string, old, new [OK]

Common Mistakes:

• Using only two parameters
• Swapping parameter order
• Assuming REPLACE works with one substring only

5. You have a table products with a column description containing the text 'color' spelled as 'colour' in some rows. Which SQL query correctly updates all 'colour' to 'color' in the description column?

hard

A. UPDATE products SET description = REPLACE('colour', 'color', description);

B. UPDATE products SET description = REPLACE(description, 'colour', 'color');

C. UPDATE products SET description = REPLACE(description, 'color', 'colour');

D. UPDATE products SET description = REPLACE(description, 'colour');

Solution

1. Step 1: Understand the goal

   We want to replace all occurrences of 'colour' with 'color' in the description column.

2. Step 2: Check each query's correctness

   UPDATE products SET description = REPLACE(description, 'colour', 'color'); correctly uses REPLACE with the column first, then old substring 'colour', then new substring 'color'. UPDATE products SET description = REPLACE('colour', 'color', description); has wrong parameter order. UPDATE products SET description = REPLACE(description, 'color', 'colour'); replaces 'color' with 'colour' (opposite). UPDATE products SET description = REPLACE(description, 'colour'); misses the new substring parameter.

3. Final Answer:

   UPDATE products SET description = REPLACE(description, 'colour', 'color'); -> Option B

4. Quick Check:

   Correct update syntax with REPLACE = B [OK]

Hint: Use REPLACE(column, old, new) in UPDATE to fix text [OK]

Common Mistakes:

• Swapping old and new substrings
• Missing new substring parameter
• Using wrong parameter order