SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Searching though text field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paulkem
Starting Member

25 Posts

Posted - 01/30/2013 :  21:16:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3701 Posts

Posted - 01/31/2013 :  08:01:37  Show Profile  Reply with Quote
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

25 Posts

Posted - 01/31/2013 :  15:44:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3701 Posts

Posted - 01/31/2013 :  16:26:43  Show Profile  Reply with Quote
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

25 Posts

Posted - 01/31/2013 :  18:15:27  Show Profile  Reply with Quote
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

25 Posts

Posted - 01/31/2013 :  18:26:03  Show Profile  Reply with Quote
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

25 Posts

Posted - 02/01/2013 :  12:59:53  Show Profile  Reply with Quote
And idea how to do this without using the splitter function?

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3701 Posts

Posted - 02/01/2013 :  17:27:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000