Update and REPLACE part of a string

sql

update

replace

sql-queries

byAnton Shumikhin·Mar 9, 2025

Use the REPLACE() function in SQL to alter substrings:

UPDATE tablename
SET columnname = REPLACE(columnname, 'oldSubstring', 'newSubstring')
WHERE columnname LIKE '%oldSubstring%';

This updates "oldSubstring" to "newSubstring" in "columnname" of "tablename" where rows contain "oldSubstring".

Precise Targeting in UPDATE

For accurate updates, narrow down to the exact rows needing changes with a WHERE clause. This prevents unnecessary row modification and preserves data quality:

UPDATE dbo.MyTable
SET MyColumn = REPLACE(MyColumn, 'oldText', 'newText')
WHERE ID <= 4 AND MyColumn LIKE '%oldText%';  -- We're only looking at IDs less or equal to 4

Confronting Type Casting Titans

If a data type mismatch blocks your way with REPLACE(), especially when handling text or ntext fields, use the SQL type-casting superpowers:

UPDATE dbo.MyTable
SET MyColumn = CAST(REPLACE(CAST(MyColumn AS nvarchar(max)), 'oldText', 'newText') AS text)
WHERE ID <= 4;  -- Assumes you've got less than 5 problems

The Wildcards Won't Work Here

REPLACE is quite particular—it doesn't get along with wildcards when searching for matches. Keep in mind to always specify the exact substring:

UPDATE dbo.MyTable
SET MyColumn = REPLACE(MyColumn, 'oldText', 'newText')
WHERE MyColumn LIKE 'old%Text';  -- This ain't a wildcard party!

Preview to Prevent Panic

Before speeding ahead with UPDATE, it's wise to do a quick SELECT and preview the changes. Here's a safe driving tip:

SELECT MyColumn, REPLACE(MyColumn, 'oldText', 'newText') AS Preview
FROM dbo.MyTable
WHERE MyColumn LIKE '%oldText%';  -- Nobody ever regrets a preflight check!

Special Character Care-taking

When special characters (like backslashes \) are part of the substring, handle them with care. SQL treats \ as an escape:

UPDATE dbo.MyTable
SET MyColumn = REPLACE(MyColumn, '123\\', 'replacementText')
WHERE MyColumn LIKE '%123\\%';  -- These aren't the droids (or special characters) SQL is looking for.

A Safety Net with Transactions

It's always safer to wrap your UPDATE statement in a transaction. This allows for a graceful rollback just in case something hits the fan:

BEGIN TRAN  -- Starting off on a safe note
UPDATE dbo.MyTable
SET MyColumn = ...
-- Indulging in update statement here
COMMIT TRAN  -- Final curtain call