;WITH Transcript (message) AS (
        SELECT '20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?'
        UNION ALL SELECT '20:32:31 [Marina Galofaro] What is the mailing address to mail a check?'
        UNION ALL SELECT '20:32:49 [Rachel] I''m happy to provide you with the mailing address.'
        UNION ALL SELECT '20:33:22 [Marina Galofaro] O kay, Thank you.'
        UNION ALL SELECT '20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.'
        UNION ALL SELECT '20:34:05 [Rachel] Please click <a href="http://company.custhelp.com/app/answers/detail/a_id/1050/kw/payment" target="_blank">here</a> to view how to mail a payment.'
        UNION ALL SELECT '20:35:07 [Rachel] Is there anything else I can help with, Marina? '
        UNION ALL SELECT '20:35:56 [Marina Galofaro] No, Thank you!'
        UNION ALL SELECT '20:36:00 [Marina Galofaro] Session Disconnected'),
    Operators (username) AS (
        SELECT 'Rachel')
SELECT message, username
    ,operator=LEFT(operator, CHARINDEX(']', operator)-1)
    ,CHARINDEX(']', operator)
    ,TextOfMessage=LTRIM(RIGHT(operator, LEN(operator)-CHARINDEX(']', operator)))
FROM Transcript
CROSS APPLY (
    SELECT SUBSTRING(message, CHARINDEX('[', message)+1, LEN(message))
    ) a(operator) 
LEFT JOIN Operators ON LEFT(operator, CHARINDEX(']', operator)-1)=username
WHERE username IS NULL

WITH Transcript (message) AS (
        SELECT TextHTML FROM Actions WHERE ID = 6831908),
    Operators (FirstName) AS (
        SELECT FirstName FROM Actions A INNER JOIN Users U on A.Agent = U.ID WHERE A.ID = 6831908)
SELECT message, FirstName
    ,operator=LEFT(operator, CHARINDEX(']', operator)-1)
    ,CHARINDEX(']', operator)
    ,TextOfMessage=LTRIM(RIGHT(operator, LEN(operator)-CHARINDEX(']', operator)))
FROM Transcript
CROSS APPLY (
    SELECT SUBSTRING(message, CHARINDEX('[', message)+1, LEN(message))
    ) a(operator) 
LEFT JOIN Operators ON LEFT(operator, CHARINDEX(']', operator)-1)=FirstName
WHERE FirstName IS NULL

;WITH Transcript (message) AS (
        SELECT '20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?'
        + char(10) + char(13) +  '20:32:31 [Marina Galofaro] What is the mailing address to mail a check?'
        + char(10) + char(13) +  '20:32:49 [Rachel] I''m happy to provide you with the mailing address.'
        + char(10) + char(13) + '20:33:22 [Marina Galofaro] O kay, Thank you.'
        + char(10) + char(13) + '20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.'
        + char(10) + char(13) + '20:34:05 [Rachel] Please click <a href="http://company.custhelp.com/app/answers/detail/a_id/1050/kw/payment" target="_blank">here</a> to view how to mail a payment.'
        + char(10) + char(13) + '20:35:07 [Rachel] Is there anything else I can help with, Marina? '
        + char(10) + char(13) + '20:35:56 [Marina Galofaro] No, Thank you!'
        + char(10) + char(13) + '20:36:00 [Marina Galofaro] Session Disconnected')
        
select * from transcript

;WITH Actions (message) AS (
        SELECT '20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?'
        + char(10) + char(13) +  '20:32:31 [Marina Galofaro] What is the mailing address to mail a check?'
        + char(10) + char(13) +  '20:32:49 [Rachel] I''m happy to provide you with the mailing address.'
        + char(10) + char(13) + '20:33:22 [Marina Galofaro] O kay, Thank you.'
        + char(10) + char(13) + '20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.'
        + char(10) + char(13) + '20:34:05 [Rachel] Please click <a href="http://company.custhelp.com/app/answers/detail/a_id/1050/kw/payment" target="_blank">here</a> to view how to mail a payment.'
        + char(10) + char(13) + '20:35:07 [Rachel] Is there anything else I can help with, Marina? '
        + char(10) + char(13) + '20:35:56 [Marina Galofaro] No, Thank you!'
        + char(10) + char(13) + '20:36:00 [Marina Galofaro] Session Disconnected')
, Transcript(Message) as
(       
	select Item 
	from Actions
	cross apply dbo.DelimitedSplit8K(replace(message, char(13), ''), char(10))
)
,
Operators (username) AS 
(
	SELECT 'Rachel'
)

SELECT message, username
    ,operator=LEFT(operator, CHARINDEX(']', operator)-1)
    ,CHARINDEX(']', operator)
    ,TextOfMessage=LTRIM(RIGHT(operator, LEN(operator)-CHARINDEX(']', operator)))
FROM Transcript
CROSS APPLY (
    SELECT SUBSTRING(message, CHARINDEX('[', message)+1, LEN(message))
    ) a(operator) 
LEFT JOIN Operators ON LEFT(operator, CHARINDEX(']', operator)-1)=username
WHERE username IS NULL