In SQL I have a table field that shows a log of events in a call. It is formatted like:

‘HH:MM:SS: Call Initiated HH:MM:SS: Call answered HH:MM:SS: Menu Main HH:MM:SS: Menu Self Service HH:MM:SS: Member Premium HH:MM:SS: One Time Payment HH:MM:SS: Transfer to agent HH:MM:SS: Disconnected [Remote Disconnect]’

I am trying to get a column that breaks this out into a row for each section within that string, like:
HH:MM:SS: Call Initiated
HH:MM:SS: Call answered
HH:MM:SS: Menu Main
HH:MM:SS: Menu Self Service
HH:MM:SS: Member Premium
HH:MM:SS: One Time Payment
HH:MM:SS: Transfer to agent
HH:MM:SS: Disconnected [Remote Disconnect]

I have had some success using the STRING_SPLIT and replacing the multi character delimiter ‘: ‘ with a single character delimiter, but the results:

DECLARE @OLDdelim varchar(2) = ‘: ‘
DECLARE @NEWdelim nchar(1) = NCHAR(9999)

SELECT CallId,CallDate,value FROM calldetail cd
CROSS APPLY STRING_SPLIT(REPLACE(cd.CallEventLog, @OLDdelim, @NEWdelim), @NEWdelim)

CURRENT RESULTS (date for the next item is after the value of the current row)

CallId CallDate value

100264416140230613 2023-06-13 06:07:53
100264416140230613 2023-06-13 Initializing 06:07:55
100264416140230613 2023-06-13 Offering 06:07:57
100264416140230613 2023-06-13 Call answered 06:08:14
100264416140230613 2023-06-13 Menu Main 06:08:17
100264416140230613 2023-06-13 Menu Self Service 06:08:21

DESIRED RESULTS

CallId CallDate LogEventTime_Description

100264416140230613 2023-06-13 06:07:53 Initializing
100264416140230613 2023-06-13 06:07:55 Offering
100264416140230613 2023-06-13 06:07:57 Call answered
100264416140230613 2023-06-13 06:08:14 Main Menu
100264416140230613 2023-06-13 06:08:17 Menu Self Service
100264416140230613 2023-06-13 06:08:21 Member Premium