Searching through text field

Question

Post reply

Searching through text field

paul.j.kemna

Ten Centuries

Points: 1076
More actions
January 30, 2013 at 7:19 pm

#394070

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

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

What happens when the customer is called Rachel?

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

paul.j.kemna Ten Centuries Points: 1076 More actions · Answer 3

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.

paul.j.kemna Ten Centuries Points: 1076 More actions · Answer 4

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

paul.j.kemna Ten Centuries Points: 1076 More actions · Answer 6

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Then you can combine that with Dwain's logic and voila!!!

;WITH Actions (message) AS (