Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
paulkem
Starting Member
28 Posts |
Posted - 2013-01-30 : 21:16:39
|
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. SELECT A.ID, A.Agent, A.TextHTML, charindex('[', TextHTML, 0) as FirstOpenBracket, charindex(']', TextHTML, 0) as FirstClosedBracket, SUBSTRING(TextHTML, CHARINDEX('[', TextHTML , 1) + 1, CASE WHEN (CHARINDEX(']', TextHTML , 0) - CHARINDEX('[', TextHTML, 0)) > 0 THEN CHARINDEX(']', TextHTML, 0) - CHARINDEX('[', TextHTML, 0) - 1 ELSE 0 END) as FirstBracketName, U.FirstName as AgentFirstNameFROM Actions A INNER JOIN Users U on A.Agent = U.IDWHERE A.[Type] = 9 AND A.CreationTime >= '2012-10-26 00:00:00' AND A.TimeAllocated > 0 AND A.TextHTML IS NOT NULL But I don’t know where to go from there. Can this even be accomplished “in line” ?I don’t think so since the number of bracketed names until I hit the customer is dynamic. Could anyone point me in the right direction?ThanksPaul |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 08:01:37
|
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)CREATE TABLE #tmp(Employee VARCHAR(32));INSERT INTO #tmp VALUES ('Rachel'); DECLARE @x VARCHAR(MAX) = '<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>';WITH cte1 AS( SELECT * FROM dbo.DelimitedSplit8K(REPLACE(REPLACE(@x,'[','|'),']','|'),'|') s),cte2 AS( SELECT TOP 1 * FROM cte1 WHERE cte1.ItemNumber%2 = 0 AND Item NOT IN (SELECT Employee FROM #tmp))SELECT * FROM cte1 c1 INNER JOIN cte2 c2 ON c1.ItemNumber = c2.ItemNumber+1DROP TABLE #tmp |
|
|
paulkem
Starting Member
28 Posts |
Posted - 2013-01-31 : 15:44:22
|
Do i just copy and paste that code and run it to create the function?Is the code in that post accurate? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 16:26:43
|
Jeff has a blog here - http://www.sqlservercentral.com/articles/Tally+Table/72993/That has the latest version of his splitter. Look for "The Final "New" Splitter Code, Ready for Testing"Yes, you can copy and paste it to a SSMS window and execute it to create the function. |
|
|
paulkem
Starting Member
28 Posts |
Posted - 2013-01-31 : 18:15:27
|
Yep, found this code earlier and ran the code to create the function. I have not had a chance to try to use it though. |
|
|
paulkem
Starting Member
28 Posts |
Posted - 2013-01-31 : 18:26:03
|
This is pretty close. The string that i really need to extract would be:<BR>20:32:31 [Marina Galofaro] What is the mailing address to mail a check? <BR> |
|
|
paulkem
Starting Member
28 Posts |
Posted - 2013-02-01 : 12:59:53
|
And idea how to do this without using the splitter function? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-01 : 17:27:24
|
quote: Originally posted by paulkem And idea how to do this without using the splitter function?
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? |
|
|
|
|
|
|
|