March 10, 2010 at 8:20 am
At my work we have a field that is basically a long string (datatype text) that is updated when a record is re-assigned in our call tracking system.
Here is an example of what that field looks like:
"Mon Mar 01 2010 05:50:49 GMT+0000 (GMT Standard Time) (4txcloud) Assign workload Mon Mar 02 2010 05:50:47 GMT+0000 (GMT Standard Time) (4txfox) Assign workload"
You can see that this record was originally assigned to a user with the user_ID of "4txcloud", then on the next day it was assigned to a user with the user_id of "4txfox".
However, sometimes records get re-assigned to the same user id, like this one:
"Mon Mar 01 2010 05:50:49 GMT+0000 (GMT Standard Time) (4txcloud) Assign workload Mon Mar 02 2010 05:50:47 GMT+0000 (GMT Standard Time) (4txcloud) Assign workload"
What I need is a way to selectively differentiate between both types. Can I put something in my WHERE clause that will look at every occurance of '(4', since all user_id's start with '(4', and only return the records where the user_id's are not the same?
Any help is greatly appreciated.
March 10, 2010 at 12:38 pm
It would be a lot easier if you'd consider storing the separate logical information in separate columns. There are many scenarios to be covered to make a string parsing like that functional each and every time under all circumstances.
Side note: "You can see that this record was originally ..." is not really obvious. It would have been if you had used separate columns with descritive names.
March 10, 2010 at 3:15 pm
Timothy
If your version really is SS2K8 then you should have a quick peek in BOL at text datatype to get a good idea of why you should change your column to VARCHAR(MAX), apart from the downright difficulty of processing text. Then, assuming that you can't implement the changes which Lutz has suggested, you need to study your data to identify start and end points of logical chunks of data in the string. Looks to me something like this:
Mon Mar 01 2010 05:50:49 GMT+0000 (GMT Standard Time) (4txcloud) Assign workload
Mon Mar 02 2010 05:50:47 GMT+0000 (GMT Standard Time) (4txfox) Assign workload
In other words, a datetime, a userid, and a task as a string. Perhaps something to correspond back to the source row - both of these would be, say, SourceRow 1.
If you have a third-party app which is sending you this data, then consider shredding it upon receipt into a sensible table structure. You will have one unmonitored process doing all the hard work and the programmers can put their feet up.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 12, 2010 at 8:24 am
I would love to change the database structure, but this is from a monster known as HP Service Manager.
HP does not support us changing either the data types or application interface of their proprietary structure in any way.
With that in mind, there may be no solution to my problem.
March 12, 2010 at 10:09 am
Assuming the string pattern at the end of the string needs to be identical to a pattern in the first half of the string, the sample code might work:
DECLARE @col VARCHAR(200)
SET @col ='Mon Mar 01 2010 05:50:49 GMT+0000 (GMT Standard Time) (4txcloud) Assign workload Mon Mar 02 2010 05:50:47 GMT+0000 (GMT Standard Time) (4txfox) Assign workload'
SELECT
CASE WHEN
PATINDEX('%' + SUBSTRING(@col,LEN(@col)-CHARINDEX('(',REVERSE(@col)),CHARINDEX('(',REVERSE(@col))+1)+'%',@col)< LEN(@col)/2
THEN 'identical' ELSE 'different' END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply