January 2, 2014 at 11:25 am
I have string which sores dates and related text.
for example:
2013/12/12: User1: Comment1. 2013/12/11: User2: Comment2 2013/12/10: followed up on 2013/12/03: between 1 to 4
I want to extract dates and respective comments. example,
2013/12/12 User1: Comment1
2013/12/11 User2: Comment2
2013/12/10 followed up on 2013/12/03: between 1 to 4
please help
Abhijit - http://abhijitmore.wordpress.com
January 2, 2014 at 11:50 am
This code is a modification from Dwain Camps' Pattern-based splitter.
I'm not sure if it's the best way to do it.:-P
DECLARE @List VARCHAR(8000) = '2013/12/12: User1: Comment1. 2013/12/11: User2: Comment2 2013/12/10: followed up on 2013/12/03: between 1 to 4'
,@Pattern VARCHAR(50) = '%[1-2][0-9][0-9][0-9]/[0-2][0-9]/[0-3][0-9]%';
WITH numbers AS (
SELECT TOP(ISNULL(DATALENGTH(@List), 0))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
Item = SUBSTRING(@List,MIN(n) - 1,2+MAX(n)-MIN(n))
FROM (
SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
FROM numbers
CROSS APPLY (
SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,10) LIKE @Pattern THEN 1 ELSE 0 END
) y
) d
WHERE Matched = 0
GROUP BY [Matched], Grouper
January 2, 2014 at 3:06 pm
In the first instance, the question has to be, why would you have this data in your DB ? You should be seperating this data out, in your business layer, and storing it as several fields in your DB. If it were me, I'd use SQL like the SQL you were given, to pull out values and store them in new columns, so you have the blob of data, and strongly typed fields that you can use instead, for queries.
January 2, 2014 at 6:10 pm
Luis Cazares (1/2/2014)
This code is a modification from Dwain Camps' Pattern-based splitter.
First of all, let's give credit where credit is due. That fast code came out of Chris Morris (ChrisM@Work as he's known around these parts). I'll take credit for the idea though (you can read about it in the 4th link in my signature articles).
Luis: Your code produces the output below, which I'm inclined to think the OP wants, however it does not match what the OP requests (his last line is actually your 2 last lines, but yours are split).
ItemNumber Item
1 2013/12/12: User1: Comment1.
2 2013/12/11: User2: Comment2
3 2013/12/10: followed up on
4 2013/12/03: between 1 to 4
I was initially thinking that using PatternSplitCM more directly might simplify the task, but after fiddling with it, the code I came up with doesn't look all that simplified. It could be because I split the date into a separate column from the comment. But for what it's worth, here it is anyway, Thanks to Luis for the lovely @Pattern you provided - just what I needed.
DECLARE @Pattern VARCHAR(50) = '%[1-2][0-9][0-9][0-9]/[0-2][0-9]/[0-3][0-9]%';
WITH SampleData (MyStr) AS
(
SELECT '2013/12/12: User1: Comment1. 2013/12/11: User2: Comment2 2013/12/10: followed up on 2013/12/03: between 1 to 4'
UNION ALL SELECT '2013/12/12: User1: Comment1. 2013/12/12: User2: Comment2'
),
GroupedData AS
(
SELECT MyStr, [Date]=REPLACE([Date], ':', '')
,[Matched], Item, ItemNumber
,rn=CASE WHEN [Date] IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY MyStr ORDER BY [Date] DESC)
ELSE ItemNumber - ROW_NUMBER() OVER (PARTITION BY MyStr, [Date] ORDER BY ItemNumber)
END
FROM
(
SELECT MyStr
,[Matched]=CASE WHEN Item LIKE @Pattern THEN 2 ELSE [Matched] END
,[Date]=CASE WHEN Item LIKE @Pattern THEN Item END
,Item, ItemNumber
FROM SampleData
CROSS APPLY dbo.PatternSplitCM(MyStr, '[0-9/:]')
) a)
SELECT MyStr, [Date]=MAX([Date])
,Comment=STUFF(
(
SELECT Item + ''
FROM GroupedData b
WHERE [Date] IS NULL AND a.MyStr = b.MyStr AND a.rn = b.rn
ORDER BY ItemNumber
FOR XML PATH(''), TYPE
).value('.','varchar(8000)'), 1, 1, '')
FROM GroupedData a
GROUP BY MyStr, rn
ORDER BY MyStr, rn;
This is the output from mine:
Date Comment MyStr
2013/12/12 User1: Comment1. 2013/12/12: User1: Comment1. 2013/12/11: User2: Comment2 2013/12/10: followed up on 2013/12/03: between 1 to 4
2013/12/11 User2: Comment2 2013/12/12: User1: Comment1. 2013/12/11: User2: Comment2 2013/12/10: followed up on 2013/12/03: between 1 to 4
2013/12/10 followed up on 2013/12/12: User1: Comment1. 2013/12/11: User2: Comment2 2013/12/10: followed up on 2013/12/03: between 1 to 4
2013/12/03 between 1 to 4 2013/12/12: User1: Comment1. 2013/12/11: User2: Comment2 2013/12/10: followed up on 2013/12/03: between 1 to 4
2013/12/12 User1: Comment1. 2013/12/12: User1: Comment1. 2013/12/12: User2: Comment2
2013/12/12 User2: Comment2 2013/12/12: User1: Comment1. 2013/12/12: User2: Comment2
I think you're going to have a devil of a time with this if you really do want that last line of output as you requested in your expected results.
Like Christian says, it is difficult to see any redeeming value in why such crude data is stored in a database column to begin with, but I understand why it may be difficult to control what users type into (for example) a remarks field. Which then leads me to ask, exactly how well formatted could you expect free-form text entry to be in a case like that? If not well-formed, then none of this code is likely to work everywhere such free-form text is present.
In case you are unfamiliar with using FOR XML PATH to concatenate strings:
Creating a comma-separated list[/url] by Wayne Sheffield
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply