I get a list of dynamic text which contains multiple dates separated by ; Need to exact extract second last date/value using substring_index.
Examples of my dynamic text:
First example of my text
2022-07-01 00:00:00;2022-10-01 00:00:00;2023-01-01 00:00:00;2023-04-01 00:00:00;
Second example of my text
2022-07-01 00:00:00;2022-10-01 00:00:00;2023-01-01 00:00:00;2023-04-01 00:00:00;2024-04-01 00:00:00;
End Result should be from first example = 2023-01-01 00:00:00 End Result should be from Second example = 2023-04-01 00:00:00
My string value are dynamic. Sometimes it contains all together 5 values, sometimes 3 values ... separated by ;
CodePudding user response：
You can use the Substring_Index as the following:
set @t='2022-07-01 00:00:00;2022-10-01 00:00:00;2023-01-01 00:00:00;2023-04-01 00:00:00;';
select Substring_Index(Substring_Index(@t, ';', -3), ';', 1) as dt
Another option is to use the right and left functions as the following:
set @t='2022-07-01 00:00:00;2022-10-01 00:00:00;2023-01-01 00:00:00;2023-04-01 00:00:00;';
select Left(Right(@t, 40), 19);