How to update only part of a field

Question

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.

Answer 1

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
...

Answer 2

I use Oracle but below is how I would try it in SQL-Server:

update tablename
set fieldname = concat(substring(fieldname, 1, (length(fieldname) - 7)), 'FY16-Q1')
where fieldname like '%FY15-Q2'

It would probably be helpful to explain what I am doing in the substring() function so here it goes:

Update fieldname to be itself but remove the unwanted 7 chars FY15-Q2 from the end and instead concat the string with FY16-Q1