CODEWITHSUNDEEP
sql-servert-sql
Cataster
Cataster

Reputation: 3541

How to simplify this replace query?

I need to change values for several specific reporting years, so this means I have to run each set statement separately with this query I currently have:

update [Table1]
set [Report Year] = replace([Report Year], '2013 LTP', '2013')
set [Report Year] = replace([Report Year], '2014 LTP', '2014')
set [Report Year] = replace([Report Year], '2015 LTP', '2015')
set [Report Year] = replace([Report Year], '2016 LTP', '2016')
set [Report Year] = replace([Report Year], '2017 LTP', '2017')
set [Report Year] = replace([Report Year], '2018 LTP', '2018')

Basically, I'm trying to remove LTP from years less than 2019.

Is there a way I can execute a query at once that does the same thing? In other words, how can I simplify the update query I have?

Edit

I need to do this for several tables, so say Table1 and Table2.

Table1 has Report Year as Primary Key, but Table 2 doesn't have that constraint.

Table1:

Report Year
2013 LTP
2014 LTP
2015 LTP
2016 LTP
2017 LTP
2018 LTP
2019 LTP
2020 LTP
2020 6+6
2021 LTP
2022 4+8
2022 BP
2022 LTP
BP19
BP20

Table2:

Report Year
2013 LTP
2014 LTP
2014 LTP
2015 LTP
2016 LTP
2017 LTP
2018 LTP
2018 LTP
2018 LTP
2019 LTP
2020 LTP
2020 LTP
2020 LTP
2020 6+6
2021 LTP
2022 4+8
2022 BP
2022 LTP
2020 6+6
2020 LTP
2020 6+6
2021 LTP
2022 4+8
2022 LTP
2022 LTP
2022 LTP
BP19
BP20
BP20

Upvotes: 0

Views: 59

Answers (2)

shawnt00
shawnt00

Reputation: 17925

case when [Report Year] not like '%2019 LTP%'
     and  [Report Year] not like '%20[23456789][0123456789] LTP%' -- tweak as necessary
    then replace([Report Year], ' LTP', '')
    else [Report Year]
end

This will work until the end of the century. It deals with text as text without needing to attempt casts to a numeric type.

An alphabetic comparison might even be good enough based on your sample data. This one also handles the year dynamically:

case when [Report Year] < cast(year(getdate() - 1) as char(4))
    then replace([Report Year], ' LTP', '')
    else [Report Year]
end

Upvotes: 1

Sander
Sander

Reputation: 4042

Take the first 4 characters from ReportYear and check if its a number. If so, convert those 4 digits to an integer and limit the selection to years before 2019. Update the selection by changing the ReportYear to the first 4 characters of that column.

Sample data

create table MyTable
(
  ReportYear nvarchar(10)
);

insert into MyTable (ReportYear) values
('2013 LTP'),
('2014 LTP'),
('2015 LTP'),
('2016 LTP'),
('2017 LTP'),
('2018 LTP'),
('2019 LTP'),
('2020 LTP'),
('2020 6+6'),
('2021 LTP'),
('2022 4+8'),
('2022 BP'),
('2022 LTP'),
('BP19'),
('BP20');

Solution

update mt
set mt.ReportYear = left(mt.ReportYear, 4)
from MyTable mt
where isNumeric(left(mt.ReportYear, 4)) = 1
  and try_convert(int, left(mt.ReportYear, 4)) < 2019;

Details and working example: fiddle.

Upvotes: 2

Related Questions