I have a text field (nvarchar max) that contains a chat transcript. I need to find the first chat entry that was not entered by one of our agents. That is, the first entry by the customer.

The transcript will look like this:<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><BR>

So the first thing I need to do is find the name between the first set of brackets and compare the contents to another table that translates an ID on this table to a name.

If the name is found, then I need to move on to the next bracketed section and find that name and compare it. If it matches again, then move on to the next bracket, and so on. Once the name does not match, then I need to extract that first line of the chat, including the HTML tags. So basically back up 13 chars from that bracket and then grab the text through the next <BR> tag.

I have something worked out for the first part to get that first bracketed name and compare it to the agent on the record.

Here is something to get you started - you can copy the code to a query window and run it. It needs you to install a string splitter function DelimitedSplit8K which can be found here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153458 (look for the post by Jeff Moden at 11/28/2010 : 17:20:21)

It would be hard and possibly unreliable. I have seen people converting strings to XML data by inserting node names into the string and then querying the XML data; but that will fail if you have any XML special characters such as &, < etc.

Another option would be to use a CLR stored procedure. What you would do is write a CLR procedure (in C# or another .Net language that takes the data and parses it.)

Is there a reason to not use string splitter? Is there any issue that you are facing?