Update a column value, replacing part of a string

sql

bulk-updates

string-functions

database-administration

byNikita Barsukov·Sep 8, 2024

Quickly update a string within a database column using the REPLACE function in SQL. This function allows for the swap of specified substrings.

To update 'foo' to 'bar' in the text_column of example_table:

UPDATE example_table
SET text_column = REPLACE(text_column, 'foo', 'bar')
WHERE text_column LIKE '%foo%';

This transformation of 'foo' into 'bar' strictly happens where 'foo' is present.

Single field domain update

When the need arises to bulk update URLs within your database, it's critical that you pay attention to detail. Convenience lies in the REPLACE function, especially if you need to update a domain but retain the filename:

-- The boss gave us a new domain, but forgot we needed to keep the filenames
UPDATE your_table
SET url_column = REPLACE(url_column, 'http://old-domain.com/images/', 'http://new-domain/pictures/')
WHERE url_column LIKE 'http://old-domain.com/images/%';

Pay keen attention to the % wildcard character—it acts as an umbrella for any sequence of characters, thereby ascertaining we only lay hands on URLs beginning with the specific pattern.

As any responsible database admin would do, always backup your database before taking such giant leaps.

Cautionary update

Undertaking bulk updates such as modifying column values demands a keen and accurate WHERE clause, so you only adjust your interest rows. For instance, when the need to replace a certain folder name in URLs arises:

-- practically everything stores in oldfolder, time to move to newfolder
UPDATE your_table
SET url_column = REPLACE(url_column, '/oldfolder/', '/newfolder/')
WHERE url_column LIKE '%/oldfolder/%';

Take time to construct the pattern in your LIKE clause. It guarantees a match for just the instances requiring a change. Verify the accuracy of the patterns to narrow down your change scope; this prevents you from making undue edits to your data.

String functions and their quirks

Appreciate that the REPLACE function is case-sensitive. Any form of inconsistent casing in your data might necessitate supplementary steps or the adoption of case conversion functions ahead of applying REPLACE. All this hassle is to conform to and update all the variations.

Advanced replacements

Not all replacements will be as straightforward. At times, you'll encounter sophisticated scenarios that call for a deeper level of precision. This is where LEFT, RIGHT, or SUBSTRING functions come in handy—they can be paired with REPLACE to give you just the level of selectivity you need for those precision tasks.

Range-constrained updates

In some instances, you may want to limit your updates to a specific range of records. For example, you might only want to update records created within a specific date range or with certain status codes. Flex your SQL muscles with compound conditions in your WHERE clause, like this:

-- Because the records from last year keep haunting us
UPDATE example_table
SET text_column = REPLACE(text_column, 'oldtext', 'newtext')
WHERE text_column LIKE '%oldtext%'
AND created_at BETWEEN '2021-01-01' AND '2021-12-31';