SELECT '<_BR>20:32:12 [Rachel] Thank you for contacting Scholastic.  How may I assist you?<_BR><_BR>20:32:31 [Marina Galofaro] What is the mailing address to mail a check?  <_BR><_BR>20:32:49 [Rachel] Im happy to provide you with the mailing address.<_BR><_BR>20:33:22 [Marina Galofaro] O kay, Thank you. <_BR><_BR>20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.<_BR><_BR>20:34:05 [Rachel] Please click <_a href="http://scholastic.custhelp.com/app/answers/detail/a_id/1050/kw/payment" target="_blank">here<_/a> to view how to mail a payment.<_BR><_BR>20:35:07 [Rachel] Is there anything else I can help with, Marina? <_BR><_BR>20:35:56 [Marina Galofaro] No, Thank you!<_BR><_BR>20:36:00 [Marina Galofaro] Session Disconnected<_BR><_BR>' as TextHTML

;WITH Actions (message) AS 
(
	;WITH Actions (message) AS 
(
	SELECT '< br>20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?< br>< br>20:32:31 [Marina Galofaro] What is the mailing address to mail a check?< br>< br>20:32:49 [Rachel] I''m happy to provide you with the mailing address.< br>< br>20:33:22 [Marina Galofaro] O kay, Thank you.< br>< br>20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.< br>< br>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.< br>< br>20:35:07 [Rachel] Is there anything else I can help with, Marina? < br>< br>20:35:56 [Marina Galofaro] No, Thank you!< br>< br>20:36:00 [Marina Galofaro] Session Disconnected< br>'
)
,Transcript(Message) as
(
	select Item 
	from Actions
	cross apply dbo.DelimitedSplit8K(replace(replace(message, '< br>< br>', char(10)), '< br>', ''), char(10))
)
,Operators (username) AS 
(
	SELECT 'Rachel'
)

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

,Operators (username) AS 
(
	select top 1
		left(substring(message, charindex('[', message) + 1, len(message)), charindex(']', substring(message, charindex('[', message) + 2, len(message)), 1))
	from Transcript
)

DECLARE @CursorID as int;/*For Cursor */
DECLARE @CursorFirstName as varchar(max);/*For Cursor */
DECLARE @CursorTextHTML as nvarchar(max);/*For Cursor */


DECLARE db_cursor CURSOR FOR
SELECT A.ID, U.FirstName, A.TextHTML
from CustServ.Multimedia.Actions A INNER JOIN CustServ.Multimedia.Users U on A.Agent = U.ID
WHERE A.[Type] = 9 AND A.CreationTime >= '2012-10-26 00:00:00' AND A.TimeAllocated > 0 AND A.TextHTML IS NOT NULL
--and A.ID = 6831190

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CursorID, @CursorFirstName, @CursorTextHTML

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @LftBracketOccurences as int = len(@CursorTextHTML) - len(replace(@CursorTextHTML, '[', ''))
DECLARE @TextHTMLTemp as nvarchar(max) = @CursorTextHTML 
DECLARE @BracketName as varchar(max) = SUBSTRING(@TextHTMLTemp, CHARINDEX('[', @TextHTMLTemp , 1) + 1, CASE WHEN (CHARINDEX(']', @TextHTMLTemp , 0) - CHARINDEX('[', @TextHTMLTemp, 0)) > 0 THEN CHARINDEX(']', @TextHTMLTemp, 0) - CHARINDEX('[', @TextHTMLTemp, 0) - 1 ELSE 0 END) 
DECLARE @LeftPointer as int = 0
DECLARE @RightPointer as int = 0


DECLARE @LftBracketPos as int  = charindex('[', @TextHTMLTemp, @LeftPointer)
DECLARE @RtBracketPos as int  = charindex(']', @TextHTMLTemp, @RightPointer)
DECLARE @LoopCounter as int = 0
DECLARE @FoundIt as int = 0

WHILE (@LoopCounter < @LftBracketOccurences) and (@FoundIt < 1)
BEGIN

IF (@CursorFirstName = @BracketName) 
BEGIN 
	--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @CursorFirstName as RecordName, @BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos

	SET @LeftPointer = @LftBracketPos +1 
	SET @RightPointer = @RtBracketPos +1 
	SET @LftBracketPos = charindex('[', @TextHTMLTemp, @LeftPointer)
	SET @RtBracketPos = charindex(']', @TextHTMLTemp, @RightPointer)
	SET @BracketName = SUBSTRING(@TextHTMLTemp, @LftBracketPos, @RtBracketPos-@LftBracketPos)
	--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @CursorFirstName as RecordName,@BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos

END 
ELSE
BEGIN
	SELECT substring(@TextHTMLTemp,@LftBracketPos-13, len(@TextHTMLTemp)) -- need to find way to get end of string!!!
	SET @FoundIt = 1
END	
--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos

SET @LoopCounter = @LoopCounter + 1
END --while


FETCH NEXT FROM db_cursor INTO @CursorID, @CursorFirstName, @CursorTextHTML
END
/* closes and cleans up after cursor */
CLOSE db_cursor
DEALLOCATE db_cursor