Jan 18, 2023 by Robert Gravelle

As you are no doubt aware, updating text values in the database is a commonplace occurrence. Nonetheless, it is a rare database administrator (DBA) that doesn't feel some trepidation upon executing batch updates against production tables. In today's blog, we'll learn how to use the SQL REPLACE() function to replace either a complete or partial string in a table column.

A Typical Scenario

Here's a screenshot of the products table from the classicmodels sample database:

Suppose that the makers of Chef Anton products have decided to enclose their products in quotation marks (""). This would require a total of 4 steps:

1. Employ the LIKE operator to identify rows with Chef Anton products.
2. Parse out the product name.
3. Add the enclosing quotation marks.
4. Convert the SELECT QUERY to an UPDATE.

Let's go over each step.

Identify Rows with Chef Anton Products

As mentioned above, we can utilize the LIKE operator to identify rows with Chef Anton products. Each of these begins with the string "Chef Anton's ", so we can search for it. To do that, we will need to escape the single quote (') character and include the multi-character "%" wildcard. Here is the resulting query and results in Navicat Premium 16:

Parse Out the Product Name

The next step is to parse out the product name so that we can enclose it within quotation marks. To do that, we can employ the LEN() function to calculate the number of characters after the "Chef Anton's " portion of the string and supply that result to the RIGHT() function:

Add the Enclosing Quotation Marks

The last step in constructing the SELECT query is to add the quotes around the product name. Having parsed out the product name, we can provide it to the REPLACE() function as the first parameter, along with the concatenated (quoted) version as the 2nd parameter:

An alternative way to achieve the same end is to simply use the CONCAT() function and feed it each part of the string as follows:

SELECT CONCAT(
         LEFT(ProductName, LENGTH('Chef Anton\'s ')),
			 	 '"',
				 RIGHT(ProductName, LENGTH(ProductName)-LENGTH('Chef Anton\'s ')), 
				 '"'
       ) AS product_name 
FROM products 
WHERE ProductName LIKE 'Chef Anton\'s %'; 

Convert the SELECT QUERY to an UPDATE

All that's left to do now is to convert our SELECT query into an UPDATE. Having executed the query as a SELECT first, we can be confident that the UPDATE statement won't affect any other rows than the ones we're interested in. Here is the UPDATE query and results confirming that only two rows were updated:

Upon refreshing the products table, we can now see our updated values:

Final Thoughts on How to Perform a Search and Replace in SQL

In this blog, we learned how to update a string in a table column using a four step process. By building up the query as a series of SELECT statements, we can minimize the risk of inadvertently changing data that we did not intend to.