How to update only part of a field

A user entered a batch of records with the wrong suffix in the record name - FY15-Q2 instead of FY16-Q1. So, the records are something like:

-LAG-FY15-Q2
-AN-FY15-Q2
-OY-FY15-Q2
-RV-FY15-Q2
etc

when they should be

-LAG-FY16-Q1
-AN-FY16-Q1
etc

There are a fairly limited number of prefixes, so I could always do multiple update statements like SET record name = LAG-FY16-Q1 WHERE record name = LAG-FY15-Q2.

It strikes me there might be a more elegant way to do this with a single update statement. Something with trim, perhaps? Complicating this is the fact that some of the prefixes are three letters and others are two.

You can try something like this:

UPDATE Set name = REPLACE(t.name, r.bad, r.good)
FROM yourTable t
INNER JOIN (values
    ('FY15-Q2', 'FY16-Q1')
    , ('FY25-Q2', 'FY36-Q1')
    , ...
) as r(bad, good)
    On t.name like '%'+bad

You can add multiple pattern after FY25-Q2.

It should work fine if you don't have multiple matches. It may not be really efficient if you have tons of rows.

If your pattern is in the middle of the string, add a % at the end as well: On t.name like '%'+bad+'%'.

Before running the full update, you should first replace UPDATE by SELECT and look at what should be done:

SELECT t.name, newname = REPLACE(t.name, r.bad, r.good), r.bad, r.good
...

StackExchange Database Administrators Q#120874, answer score: 7