Select Query to comma seperated string.

Question

Post reply

Select Query to comma seperated string.

nilesh k

Default port

Points: 1457
More actions
August 24, 2009 at 4:48 am

#133124

Hi All
I have Nvarchar Column in a table which contains values like
1,2,3,4
1,4
25,26,27,28,29,30,31,32
2
I want to find out number of Rows in a table containg only '2'.
(ie. Query should return me 2 as Result in Above case)
i have tried 'IN' and 'LIKE' operators ,However both are failing me
to give correct results.
If anyone can suggest me on this!!!

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Are you not making this more complicated that it needs to be ?

Select * from table where col = '2'

Will do what you need.

Clear Sky SQL
My Blog[/url]

nilesh k Default port Points: 1457 More actions · Answer 2

Hi,

This query will return me only 1 record. ie. Record with only 2 in it.

Regards,

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Isnt that what you wanted ?

I want to find out number of Rows in a table containg only '2'.

Clear Sky SQL
My Blog[/url]

nilesh k Default port Points: 1457 More actions · Answer 4

Nope.

I wanted number of rows containing ONLY 2

Regards,

David Burrows SSC Guru Points: 65142 More actions · Answer 5

If what you posted is interpreted as 'result should show the 2 rows which contains the digit 2' then

Select * from table where ','+col+',' LIKE '%,2,%'

and

Select COUNT(*) from table where ','+col+',' LIKE '%,2,%'

will give you the number of rows

But performance will be bad as a table or index scan will be required

*Editied to remove = signs :blush:

Far away is close at hand in the images of elsewhere.
Anon.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Still not clear ...

Please post a script to create the table and insert values

Clear Sky SQL
My Blog[/url]

Andeavour SSCrazy Points: 2600 More actions · Answer 7

That's what Dave's given you. I think what you mean is the number of rows that contain a "2", but not "12" or "20" etc. So the row 1,2,3,4 would count, but 10,11,12 would not...

You could search for where (col = '2' or col = ',2' or col = ',2,' or col = '2,'). It's very messy, and could probably be done better in a case statement or a substring.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

So, to get the number of rows you'd just modify the query to do a count

SELECT COUNT(*)

FROM table

WHERE col = '2'

It's just a straight aggregate function. If you need to add other columns besides the count, simply GROUP BY the appropriate column.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

drop table #t1

go

create table #t1

(

RowId integer,

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'1,2,3,4')

insert into #t1 values(2,'4')

insert into #t1 values(3,'25,26,27,28,29,30,31,32')

insert into #t1 values(4,'2')

go

select * from #t1 where ValueStr = '2'

go

select * from #t1 where ValueStr like '%2%'

With this are you expecting Rows with the id of 1 and 4 or only 4 ????

Clear Sky SQL
My Blog[/url]

nilesh k Default port Points: 1457 More actions · Answer 10

Id of 1 and 4.

I should get result as

RowId ValueStr

1 1,2,3,4

4 2

Regards,

Nilesh.

nilesh k Default port Points: 1457 More actions · Answer 11

Id of 1 and 4

I should get result as

RowId ValueStr

1 1,2,3,4

4 2

gugan_ta SSCommitted Points: 1649 More actions · Answer 12

gugan_ta

SSCommitted

Points: 1649

August 24, 2009 at 6:09 am

#1043561

hI,

This will work

like '[2]' or i like '%,2,%'

joeroshan SSChampion Points: 10377 More actions · Answer 13

if apply David Burrows's suggestion in the Dave's code it would be

select * from #t1 where ','+ValueStr+',' like '%,2,%'

no?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Andeavour SSCrazy Points: 2600 More actions · Answer 14

If the answer to that is 1 & 4, then Dave had is spot on with:

drop table #t1

go

create table #t1

(

RowId integer,

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'1,2,3,4')

insert into #t1 values(2,'4')

insert into #t1 values(3,'25,26,27,28,29,30,31,32')

insert into #t1 values(4,'2')

go

select * from #t1 where ','+ValueStr+',' LIKE '%,2,%'

go

That will return 1 & 4. If you adjust that to

select count (*) from #t1 where ','+ValueStr+',' LIKE '%,2,%'

go

then you'll get your count.