Extract data from string column based on delimiter

Question

I have a column with value like this.

Declare @Table TABLE (ID int, Val varchar(100))
Insert into @Table
Select 1, 'happy_Summer_Holiday_by_Jan_2024.xlsx'

I want to dynamically extract Jan as Month and 2024 as year.

Answer 1

Assuming that the Month and Year are always the last parts of the filename (as in the single example), I would do this.

Declare @Table TABLE (ID int, Val varchar(100))
Insert into @Table
Select 1, 'happy_Summer_Holiday_by_Jan_2024.xlsx'
;WITH CTE_Split AS
 (
 SELECT T.ID
 , T.Val 
 , REVERSE(P.[value]) AS StringPart
 , P.ordinal
 FROM @Table AS T
 OUTER APPLY STRING_SPLIT(REVERSE(T.Val), '_', 1) AS P
 )
, CTE_UP AS
 (
 SELECT ID
 , Val
 , LEFT([1], CHARINDEX('.', [1], 0)-1) AS [Year]
 , [2] AS [Month]
 FROM CTE_Split AS S
 PIVOT (MAX(StringPart) FOR ordinal IN ([1], [2])) AS pvt 
 )
SELECT * FROM CTE_Up 

• user2370668: Thank you Jonathan!!! Much appreciated for help