Parse CSV from a single cell - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parse CSV from a single cell

Author

Topic

m0use
Starting Member

1 Post

Posted - 2009-04-23 : 18:06:48

The way my db is set up, one particular table has CSVs populating a single cell, ex: 106,110,112,114,117,123,131,136,141,142,152,154
These are primary keys to another table. What I want to be able to do is use this string in another select statement.
This doesn't seem to work....

SELECT * FROM Member
WHERE Member.ID IN
(SELECT MemberID FROM EmployerList
WHERE EmployerID = @Employer)

For some reason, this will only return exactly 1 row every time, in the above case it would only be #106.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-24 : 00:31:39

make use of these
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&SearchTerms=split
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-24 : 02:22:29

Try this too, you may get solution.

DECLARE @EmloyerList table(ID int, List varchar(50))
declare @Member table(ID char(1), Name varchar(10))
insert into @Member values(1, 'A')
insert into @Member values(2, 'B')
insert into @Member values(3, 'C')
insert into @Member values(4, 'D')
insert into @Member values(5, 'E')
insert into @Member values(6, 'f')
insert into @Member values(7, 'g')

insert into @EmloyerList values(1,'3,4,6,7')
insert into @EmloyerList values(2,'1,2,5')

declare @list varchar(50)
set @list=(select list from @EmloyerList where id =1)

select * from @member where charindex( id + ',' , @list + ',',0) > 0
select * from @member where @list + ',' like '%' + id +',%'

Kokkula
Starting Member

41 Posts

Posted - 2009-04-24 : 02:25:09

Hello,

The query should be like below...


SELECT * 
FROM Member
WHERE ',' + (SELECT MemberID FROM EmployerList WHERE EmployerID = @Employer) + ',' LIKE '%,'+ CAST(Member.ID AS NVARCHAR) + ',%'

Hope helpful...

Thanks,
Pavan

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-24 : 02:38:40

Hi,Pavan
Thanks for the immediate reply.

It seems that Performance will be affected,
since, Select with Where clause is executed for each row

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-24 : 02:39:39

Also search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-24 : 02:40:36

quote:
Originally posted by khtan

make use of these
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&SearchTerms=split
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Tan, where have you been for long time?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources