1

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 ;

Karim Ali
  • 2,243
  • 6
  • 23
  • 31

1 Answers1

1

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);

Demo

ahmed
  • 9,071
  • 3
  • 9
  • 22