SQL Replace & Concat based off date-CodePudding

I want the REPLACE function to detect years before 2022 and label them as old. I wanted to know if there's also a way to do both REPLACE and CONCAT function in the same data column in SQL?

The code I have so far is:

SELECT REPLACE(CONCAT(Year,Model), IF(Year>2021;1;0;){Year.value="New"};IF(Year<2022;1;0;){Year.value="Old"})

Below is a tab-delimited table that doesn't have the exact values, but it works under the same data format I'm going for. What I like to have is the last column values of "Replace_and_Concat".

Year    Model   Concat  Replace_and_Concat
2015    FT  2015, FT    OLD FT
2016    TR  2016, TR    OLD TR
2017    TR  2017, TR    OLD TR
2018    TR  2018, TR    OLD TR
2019    GG  2019, GG    OLD GG
2020    FT  2020, FT    OLD FT
2021    TR  2021, TR    OLD TR
2022    TR  2022, TR    NEW TR
2022    GG  2022, GG    NEW GG
2022    FT  2022, FT    NEW FT
2022    TR  2022, TR    NEW TR
2022    TR  2022, TR    NEW TR
2022    TR  2022, TR    NEW TR

CodePudding user response：

You can use a case expression; and I'd use the concatenation operator rather than the concat function, which gets messy with multiple values to put together:

select year, model,
  year || ', ' || model as result1,
  case when year< 2022 then 'OLD' else 'NEW' end || ' ' || model as result2
from your_table

If you want to use the current year rather than a fixed value of 2022 you can extract that from the system date:

select year, model,
  year || ', ' || model as result1,
  case when year< extract(year from sysdate)
    then 'OLD' else 'NEW' end || ' ' || model as result2
from your_table

db<>fiddle

(I've used generic aliases for the column expressions; partly because 'concat and replace' isn't quite what's really happening, partly because using a function name ('concat') as a column alias might be confusing, though it is allowed.)