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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Searching though text field

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 AgentFirstName
FROM Actions A INNER JOIN 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

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?

Thanks

Paul

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+1


DROP TABLE #tmp
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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>

Go to Top of Page

paulkem
Starting Member

28 Posts

Posted - 2013-02-01 : 12:59:53
And idea how to do this without using the splitter function?

Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -