SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching through text field


Searching through text field

Author
Message
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 155
I have tried a bunch of those tags and they all still process the HTML tags in my example.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27098 Visits: 17557
paul.j.kemna (1/31/2013)
I have tried a bunch of those tags and they all still process the HTML tags in my example.


So put in a space or an underscore or something so you can post it. Just let us know what we need to do to fix it.

_______________________________________________________________

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.

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)
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 155
OK, i put an underscore inside every HTML tag. This is what the field looks like. I need to find the first customer entry, including the HTML tags at the beginning and end. In the case below, it would be

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

I will determine whether it is the customer or not by comparing the value between the [] to a value on another table via join on an ID on this table to an ID on that table. (see original post).



SELECT '<_BR>20:32:12 [Rachel] Thank you for contacting Scholastic. 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] Im 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://scholastic.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>' as TextHTML



paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 155
I don't get what "operator" is in these examples. I would also prefer to not use the splitter function.

I am to the point where i may have to rely on a a cursor and just parse the text line by line.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27098 Visits: 17557
paul.j.kemna (2/1/2013)
I don't get what "operator" is in these examples. I would also prefer to not use the splitter function.

I am to the point where i may have to rely on a a cursor and just parse the text line by line.


paul.j.kemna (2/1/2013)
I don't get what "operator" is in these examples. I would also prefer to not use the splitter function.

I am to the point where i may have to rely on a a cursor and just parse the text line by line.


Well here is the rub. Not only is a cursor going to perform far worse, you have only 1 row of data so you can't use a cursor anyway. You need to split your text lines into separate rows first.

This seems to work on your example.


;WITH Actions (message) AS
(
;WITH Actions (message) AS
(
SELECT '< 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>'
)
,Transcript(Message) as
(
select Item
from Actions
cross apply dbo.DelimitedSplit8K(replace(replace(message, '< br>< br>', char(10)), '< br>', ''), char(10))
)
,Operators (username) AS
(
SELECT 'Rachel'
)

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



_______________________________________________________________

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.

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)
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 155
Ok, so this is pretty cool. I wish i knew how it worked.

It looks like the sample code had an extra ;WITH Actions (message) AS ( so I removed it.

I know that a cursor would be incredibly less inefficient but i am not sure what you mean about "one row". This is going to run across a table. Can't i step through each record and parse through the text (not sure what that would look like yet).

So how would I apply this code across the dataset (a join of two tables, Actions and Users, joined on Actions.UserID = Users.UserID?)

I assume that i would replace the SELECT '' that contains the sample text with a SELECT TextHTML FROM Actions? And then maybe SELECT FirstName FROM Users under the Operators(username) AS? But then what ties the datasets together to find out of the name associated to the UserID on the Actions record matches the name found in the brackets?

Argh.

PK
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27098 Visits: 17557
What I mean by one row is your sample data is only 1 row. It is a long string with a bunch of html tags in it.

I added another twist to this so that you don't have to know the operators name. Instead it assumes the first name it finds is the person you want to exclude. The only change is the Operators cte pulls the data dynamically instead of being hardcoded.


,Operators (username) AS
(
select top 1
left(substring(message, charindex('[', message) + 1, len(message)), charindex(']', substring(message, charindex('[', message) + 2, len(message)), 1))
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.

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)
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 155
OK. Yeah, i thought about making that assumption as well. At this point I am not sure if that is a fair assumption though.

Here is what i have come up with so far with the cursor. So far it actually seems to be working. One thing i need to work out yet is how to get to the end of that first customer chat line. For now i am just finding the first non matching name, and they backing up 13 chars from that bracket position to get the BR tag and the timestamp, and then grabbing everything after that. While this may not be the most efficient, I am super stoked that I have managed to get this close to the goal!



DECLARE @CursorID as int;/*For Cursor */
DECLARE @CursorFirstName as varchar(max);/*For Cursor */
DECLARE @CursorTextHTML as nvarchar(max);/*For Cursor */


DECLARE db_cursor CURSOR FOR
SELECT A.ID, U.FirstName, A.TextHTML
from CustServ.Multimedia.Actions A INNER JOIN CustServ.Multimedia.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
--and A.ID = 6831190

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CursorID, @CursorFirstName, @CursorTextHTML

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @LftBracketOccurences as int = len(@CursorTextHTML) - len(replace(@CursorTextHTML, '[', ''))
DECLARE @TextHTMLTemp as nvarchar(max) = @CursorTextHTML
DECLARE @BracketName as varchar(max) = SUBSTRING(@TextHTMLTemp, CHARINDEX('[', @TextHTMLTemp , 1) + 1, CASE WHEN (CHARINDEX(']', @TextHTMLTemp , 0) - CHARINDEX('[', @TextHTMLTemp, 0)) > 0 THEN CHARINDEX(']', @TextHTMLTemp, 0) - CHARINDEX('[', @TextHTMLTemp, 0) - 1 ELSE 0 END)
DECLARE @LeftPointer as int = 0
DECLARE @RightPointer as int = 0


DECLARE @LftBracketPos as int = charindex('[', @TextHTMLTemp, @LeftPointer)
DECLARE @RtBracketPos as int = charindex(']', @TextHTMLTemp, @RightPointer)
DECLARE @LoopCounter as int = 0
DECLARE @FoundIt as int = 0

WHILE (@LoopCounter < @LftBracketOccurences) and (@FoundIt < 1)
BEGIN

IF (@CursorFirstName = @BracketName)
BEGIN
--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @CursorFirstName as RecordName, @BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos

SET @LeftPointer = @LftBracketPos +1
SET @RightPointer = @RtBracketPos +1
SET @LftBracketPos = charindex('[', @TextHTMLTemp, @LeftPointer)
SET @RtBracketPos = charindex(']', @TextHTMLTemp, @RightPointer)
SET @BracketName = SUBSTRING(@TextHTMLTemp, @LftBracketPos, @RtBracketPos-@LftBracketPos)
--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @CursorFirstName as RecordName,@BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos

END
ELSE
BEGIN
SELECT substring(@TextHTMLTemp,@LftBracketPos-13, len(@TextHTMLTemp)) -- need to find way to get end of string!!!
SET @FoundIt = 1
END
--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos

SET @LoopCounter = @LoopCounter + 1
END --while


FETCH NEXT FROM db_cursor INTO @CursorID, @CursorFirstName, @CursorTextHTML
END
/* closes and cleans up after cursor */
CLOSE db_cursor
DEALLOCATE db_cursor



Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27098 Visits: 17557
I am happy you are figuring this out. I am however sad that you were shown so many non-cursor based ways to do this and yet you chose to use a cursor.

That is sort of like getting to the race track without a car, you were scared to drive a Ferrari because you have never driven one, then you were taught how to drive it and you said "no thanks, I think I will stick with this Volkswagen Bug, it is slower than molasses on the tundra but it is comfortable".

_______________________________________________________________

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.

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)
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 155
Well that burst my bubble. I have not given up on the suggestions you gave. I just don't understand them and cannot figure out how to make them worth with my dataset. Yes, this is due to my lack of knowledge. I wanted to at least have SOMETHING that works, and that I understand, in my pocket. That does not mean that this is the code that I will go with. I have never used WITH or CROSS APPLY before and I don't know if i will be able to deploy the DelimitedSplit8K function in our environment.

I very much do appreciate your help, but this project is on a timeline and like I say, I wanted something that was at least functional as a fall back. I will need to discuss all of my options with my boss. He may understand your code and recommend going with it and may be able to explain it, but I wanted something to show that I have at least been working on this within my skill set.

Can you explain (or refer me to a reference) that would help explain what is going on in your example? I mean, even if it did work perfectly in my situation, I don't like using code that I don't fully understand.

PK
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search