UPDATE and REPLACE Part of a String in SQL

1. Overview

In this tutorial, we’ll see how to replace every occurrence of a substring in a string using the SQL statements UPDATE and REPLACE.

The code has been tested on MySQL, SQL Server, and PostgreSQL.

2. Using UPDATE With REPLACE

Let’s look at some strings from a sample Student table in our University database:

SELECT name 
FROM Student 
WHERE id BETWEEN 1000 AND 1010;

Here is the result:

+-----------------+
| name            |
+-----------------+
| John Liu        |
| Rita Ora        |
| Philip Lose     |
| Samantha Prabhu |
+-----------------+

Let’s say we want to replace every occurrence of Liu with Smith:

UPDATE Student
SET name = REPLACE(name, 'Liu', 'Smith');

The REPLACE function here finds all occurrences of the string Liu in the name column and replaces them with the string Smith. This means that every occurrence of Liu in the name is replaced, even if it occurs more than once in a single name.

By repeating the SELECT query we saw earlier, we notice the change in the first name:

+-----------------+
| name            |
+-----------------+
| John Smith      |
| Rita Ora        |
| Philip Lose     |
| Samantha Prabhu |
+-----------------+

As a final note, the REPLACE syntax only applies to strings, not numbers or dates.

2.1. Testing Multiple Occurrences in the Same String

Let’s add a row with the name Marcus Liu Liu to test if all occurrences of Liu are replaced:

INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date) 
VALUES (2020, 'Marcus Liu Liu', 9998887776, '2003-07-22', '2022-01-15', '2025-06-15');

UPDATE Student
SET name = REPLACE(name, 'Liu', 'Smith');

SELECT name
FROM Student
WHERE id = 2020;

The result is as expected, confirming the default behavior of REPLACE:

+--------------------+
| name               |
+--------------------+
| Marcus Smith Smith |
+--------------------+

On the other hand, if we only want to replace the first occurrence of Liu in a string, we can’t use REPLACE. This task is more complicated because there isn’t a single SQL query that works identically in MySQL, SQL Server, and PostgreSQL.

2.2. Optimization With WHERE

The previous query performs the REPLACE operation on all rows in the Student table, whether or not they contain the string Liu. This can be inefficient, especially if the table is large because all rows are updated even if the value of the name field doesn’t change.

If the name column is indexed, the WHERE clause can benefit from using the index to quickly find rows to update. Let’s remember that REPLACE doesn’t need wildcards, but WHERE does:

UPDATE Student
SET name = REPLACE(name, 'Liu', 'Smith')
WHERE name LIKE '%Liu%';

The WHERE name LIKE ‘%Liu%’ clause selects only those rows where the name column contains the string Liu at any position. The % character is a wildcard that represents zero or more characters, so %Liu% matches any string that contains Liu.

Updating without a WHERE clause could result in locking more rows, potentially causing more block contention and wait time for other database operations.

3. Conclusion

In this article, we learned how to use SQL statements to replace any occurrence of a substring within a string, focusing on UPDATE and REPLACE.

We started with a simple example using a Student table, replacing the substring Liu with Smith. The REPLACE function effectively changed all instances of the substring within the column. We noted that REPLACE can’t replace only the first occurrence of a substring, which requires more complex queries and varies from database to database.

Finally, we emphasized the importance of using a WHERE clause with LIKE to optimize the update operation. This ensures that only relevant rows are updated, improving efficiency and avoiding unnecessary row locks.