Ok, I admit, I have no idea what this is doing.

I tweaked the code to what i thought would work (note that i added the WHERE ID = 6831908 just to limit to one row for testing)

But I get nothing back. Also, i do need to include the HTML tags before and after the first chat line from the customer.

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