March 1, 2012 at 9:53 pm
I have a column in which I have separated Values
Example
Column A
==========
1--------- 60,59,20,18,15,12,7
2--------- 14,13,12,7,1
3--------- 15,14,13,12,1
4--------- 202,41,40,14,13,2,7,1
5--------- 17,11,2
6--------- 152,11,2
7--------- 65,27,17,11,2
8--------- 157,152,17,2
9--------- 72,61,41,15,14,13,2,7,1
10--------- 41,15,13,12,7,1
What I need in this I want those rows who contain only 12 values
like get rows 1,2,3 & 10
Thanks in Advance
Regards,
Naseer Ahmed
March 1, 2012 at 10:10 pm
Here is one way:
First setting up test data:
IF OBJECT_ID('TempDB..#Sample') IS NOT NULL
DROP TABLE #Sample ;
CREATE TABLE #Sample
( ID INT IDENTITY ,StringVARCHAR(200)) ;
INSERT INTO #Sample (String)
SELECT '60,59,20,18,15,12,7'
UNION ALL SELECT '14,13,12,7,1'
UNION ALL SELECT '15,14,13,12,1'
UNION ALL SELECT '202,41,40,14,13,2,7,1'
UNION ALL SELECT '17,11,2'
UNION ALL SELECT '152,11,2'
UNION ALL SELECT '65,27,17,11,2'
UNION ALL SELECT '157,152,17,2'
UNION ALL SELECT '72,61,41,15,14,13,2,7,1'
UNION ALL SELECT '41,15,13,12,7,1' ;
Naseer, setting up sample data like this took me around 15 mins; had you put that in your post, i would jumped onto working on the solution rather than wasting time in setting up..
Anyways, here is the actual code now
; WITH CTE AS
(
SELECT id
,String
,NewStr = '$'+String+'$' -- Assuming $ symbol will not exists in any of the rows
FROM #Sample
)
SELECT ID , String
FROM CTE
WHERE CHARINDEX('$12,', NewStr) > 0
OR CHARINDEX(',12,', NewStr) > 0
OR CHARINDEX(',12$', NewStr) > 0
This is not going to perform efficiently at all, but will do the task.
March 1, 2012 at 10:21 pm
At first thought I was going to say to use the Delimited8K string splitter for this one and then count the number of results. However I think it is going to be faster to search for all rows that have 11 comma's in them. You indicate that you're not interested in any specific values, just that there are 12 of them.
Searching for the rows that have 11 comma's is most easily done by using a tally table to split the string into characters, number each one using the row_number() function and then get the highest number at which a comma was found. Only the rows that return 11, must be selected. Had you given us some sample data plus table to play with, I would have written you a nice example. Now you'll have to do it yourself. I did not read the specs correctly. Sorry.
March 1, 2012 at 10:26 pm
Hey R.P.R , the OP does not want the rows that have 12 values , instead the rows that contain the value 12 ; look at the expected output and the number of values in the rows of the expected output. So Jeff's DelimitedSplitter will smoke this easily.
Naseer, here is another way of doing it.
SELECT *
FROM #Sample S
-- Using a function called "DelimitedSplit8K"
CROSS APPLY dbo.DelimitedSplit8K(S.String , ',') CrsApp
WHERE CrsApp.Item = 12
To learn more about the function, please read here Jeff's DelimitedSplit8k[/url]
March 2, 2012 at 12:49 am
Thanks, It resolve my problem.
You guys are marvelous.
Regards,
Naseer Ahmed
March 2, 2012 at 4:55 am
Thanks, this saves my time as well.
Vinit S
NET Programmer
March 2, 2012 at 6:57 am
ColdCoffee (3/1/2012)
Hey R.P.R , the OP does not want the rows that have 12 values , instead the rows that contain the value 12 ; look at the expected output and the number of values in the rows of the expected output. So Jeff's DelimitedSplitter will smoke this easily.Naseer, here is another way of doing it.
SELECT *FROM #Sample S
-- Using a function called "DelimitedSplit8K"
CROSS APPLY dbo.DelimitedSplit8K(S.String , ',') CrsApp
WHERE CrsApp.Item = 12
To learn more about the function, please read here Jeff's DelimitedSplit8k[/url]
From what I can see, the query can be as simple as:
select * from #Sample
where CHARINDEX(',12,',',' + String + ',',0) >0
March 2, 2012 at 7:56 am
ColdCoffee (3/1/2012)
Hey R.P.R , the OP does not want the rows that have 12 values , instead the rows that contain the value 12 ; look at the expected output and the number of values in the rows of the expected output. So Jeff's DelimitedSplitter will smoke this easily...
🙂 I ran out of time this morning and misread the OP's intentions.
March 2, 2012 at 9:51 am
Eugene Elutin (3/2/2012)
ColdCoffee (3/1/2012)
Hey R.P.R , the OP does not want the rows that have 12 values , instead the rows that contain the value 12 ; look at the expected output and the number of values in the rows of the expected output. So Jeff's DelimitedSplitter will smoke this easily.Naseer, here is another way of doing it.
SELECT *FROM #Sample S
-- Using a function called "DelimitedSplit8K"
CROSS APPLY dbo.DelimitedSplit8K(S.String , ',') CrsApp
WHERE CrsApp.Item = 12
To learn more about the function, please read here Jeff's DelimitedSplit8k[/url]
From what I can see, the query can be as simple as:
select * from #Sample
where CHARINDEX(',12,',',' + String + ',',0) >0
Hello Eugene, i had posted similar approach already on the same thread; i doubt if it will preform well.
ColdCoffee (3/1/2012)
; WITH CTE AS(
SELECT id
,String
,NewStr = '$'+String+'$' -- Assuming $ symbol will not exists in any of the rows
FROM #Sample
)
SELECT ID , String
FROM CTE
WHERE CHARINDEX('$12,', NewStr) > 0
OR CHARINDEX(',12,', NewStr) > 0
OR CHARINDEX(',12$', NewStr) > 0
This is not going to perform efficiently at all, but will do the task.
March 2, 2012 at 10:04 am
ColdCoffee (3/2/2012)
...Hello Eugene, i had posted similar approach already on the same thread; i doubt if it will preform well.
...
Which one? Yours or my one? Compare to what?
My one does it in a kind of similar manner to yours but in much shorter code.
Will it perform well?
I think it's wrong question for now, as with only details given by OP, we don't know what set of data he has to make considetrations for any performance issues (he may have a table of 100 rows...).
So, the right question for now is: Will it perform good enough for OP?
At the end, splitting the string will still need to do full-table scan + splitting itself.
For the question asked, I cannot see any better approach other than simply to do what I've posted. Lets wait for OP to respond.
March 2, 2012 at 11:34 am
Mine and urs both will do a table-scan, as is with the case of Splitter. But, urs look tidier and i forgot totally i can use comma to concatenate (i used $ symbol, which i think is "DUH" now :-D) hmm... lets wait for the OP then..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply