Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Searching through text field Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 7:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
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:

20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?

20:32:31 [Marina Galofaro] What is the mailing address to mail a check?

20:32:49 [Rachel] I'm happy to provide you with the mailing address.

20:33:22 [Marina Galofaro] O kay, Thank you.

20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.

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.

20:35:07 [Rachel] Is there anything else I can help with, Marina?

20:35:56 [Marina Galofaro] No, Thank you!

20:36:00 [Marina Galofaro] Session Disconnected



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
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
Post #1413818
Posted Wednesday, January 30, 2013 9:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
Perhaps this will help you find the path?

;WITH Transcript (message) AS (
SELECT '20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?'
UNION ALL SELECT '20:32:31 [Marina Galofaro] What is the mailing address to mail a check?'
UNION ALL SELECT '20:32:49 [Rachel] I''m happy to provide you with the mailing address.'
UNION ALL SELECT '20:33:22 [Marina Galofaro] O kay, Thank you.'
UNION ALL SELECT '20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.'
UNION ALL SELECT '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.'
UNION ALL SELECT '20:35:07 [Rachel] Is there anything else I can help with, Marina? '
UNION ALL SELECT '20:35:56 [Marina Galofaro] No, Thank you!'
UNION ALL SELECT '20:36:00 [Marina Galofaro] Session Disconnected'),
Operators (username) AS (
SELECT 'Rachel')
SELECT message, username
,operator=LEFT(operator, CHARINDEX(']', operator)-1)
,CHARINDEX(']', operator)
,TextOfMessage=LTRIM(RIGHT(operator, LEN(operator)-CHARINDEX(']', operator)))
FROM Transcript
CROSS APPLY (
SELECT SUBSTRING(message, CHARINDEX('[', message)+1, LEN(message))
) a(operator)
LEFT JOIN Operators ON LEFT(operator, CHARINDEX(']', operator)-1)=username
WHERE username IS NULL





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1413833
Posted Thursday, January 31, 2013 7:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 832, Visits: 2,683
What happens when the customer is called Rachel?




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1414112
Posted Thursday, January 31, 2013 12:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
Great question. In most cases, the customer will be first and last name. But since this is not guaranteed, yes, there will be situations where the wrong line is picked up. We will just have to explain that this is not perfect. There really are no other options at this point.
Post #1414283
Posted Thursday, January 31, 2013 1:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
Ok, I admit, I have no idea what this is doing.

I tweaked the code to what i thought would work (note that i added the WHERE ID = 6831908 just to limit to one row for testing)

But I get nothing back. Also, i do need to include the HTML tags before and after the first chat line from the customer.



WITH Transcript (message) AS (
SELECT TextHTML FROM Actions WHERE ID = 6831908),
Operators (FirstName) AS (
SELECT FirstName FROM Actions A INNER JOIN Users U on A.Agent = U.ID WHERE A.ID = 6831908)
SELECT message, FirstName
,operator=LEFT(operator, CHARINDEX(']', operator)-1)
,CHARINDEX(']', operator)
,TextOfMessage=LTRIM(RIGHT(operator, LEN(operator)-CHARINDEX(']', operator)))
FROM Transcript
CROSS APPLY (
SELECT SUBSTRING(message, CHARINDEX('[', message)+1, LEN(message))
) a(operator)
LEFT JOIN Operators ON LEFT(operator, CHARINDEX(']', operator)-1)=FirstName
WHERE FirstName IS NULL


Post #1414317
Posted Thursday, January 31, 2013 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 13,121, Visits: 11,954
This is mostly due to us being forced to speculate on your table. Dwain assume you have multiple rows but my guess is your table holds the entire discussion more in a format like this?

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

select * from transcript



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414321
Posted Thursday, January 31, 2013 1:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
Oh hell, i see the problem. I posted the raw data in my first post, but it acted on the HTML tags. I did not even notice that.

I cant seen to get it to post with the HTML tags.



Post #1414324
Posted Thursday, January 31, 2013 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 13,121, Visits: 11,954
Then you can combine that with Dwain's logic and voila!!!

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

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


You can find the DelimitedSplit8K by following the article in my signature about splitting strings.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414325
Posted Thursday, January 31, 2013 1:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:35 PM
Points: 43, Visits: 99
Oh my gosh how do i post this text field contents without it running the HTML tags???
Post #1414326
Posted Thursday, January 31, 2013 1:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 13,121, Visits: 11,954
paul.j.kemna (1/31/2013)
Oh my gosh how do i post this text field contents without it running the HTML tags???


I think if you put it inside the code tag it should work ok.

<-- Use if IFCode shortcuts over there on the left when posting.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414330
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse