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 Thursday, January 31, 2013 2:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:08 AM
Points: 48, Visits: 123
I have tried a bunch of those tags and they all still process the HTML tags in my example.
Post #1414350
Posted Thursday, January 31, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 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 #1414358
Posted Thursday, January 31, 2013 4:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:08 AM
Points: 48, Visits: 123
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


Post #1414386
Posted Friday, February 1, 2013 10:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:08 AM
Points: 48, Visits: 123
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.
Post #1414765
Posted Friday, February 1, 2013 12:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 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 #1414794
Posted Friday, February 1, 2013 12:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:08 AM
Points: 48, Visits: 123
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
Post #1414800
Posted Friday, February 1, 2013 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 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 #1414806
Posted Friday, February 1, 2013 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:08 AM
Points: 48, Visits: 123
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


Post #1414845
Posted Friday, February 1, 2013 1:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 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 #1414847
Posted Friday, February 1, 2013 2:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:08 AM
Points: 48, Visits: 123
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
Post #1414850
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse