Generate results like '2015-16 season' for values like '1516REG' for this value-CodePudding

As of now I'm handling the situation as shown below. But it is going to be a problem going forward in the future. Do I need to come up with a new table and do a join or is there any regex or substring I can use in the select statement?

I'm having a table with some values like this. So when a new value like 2223REG comes up I need to return 2022-23 Season.

season
-------
2021REG \n
2122REG \n
1516REG \n

CodePudding user response：

I would remove that case expression. It is cumbersome and not at all dynamic. You could use STUFF for this. Might a need a little tweaking if you plan on running this code in 80 years. But it will work fine until the year 2100.

declare @Something table(SomeVal varchar(20))

insert @Something
select '2021REG \n' union all
select '2324REG \n'

select '20'   stuff(left(s.SomeVal, 4), 3, 0, '-')   ' Season'
from @Something s