joining a field with csv data in it

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

SQL Server 2005 Forums

Transact-SQL (2005)

joining a field with csv data in it

Author

Topic

stormcandi
Starting Member

46 Posts

Posted - 2007-06-07 : 14:19:25

I have a field that has PK data from another table stored in it. However there are muliple PKs from this table in the field. I need to sort out the PKs and am having problems with the join. What I have so far is this:

select 
	* 
from Table1 svc inner join 
		Table2 hm ON (hm.services like ('%,' + Convert(varchar(2),svc.RecID) + ',%'))

The values in hm.services field can be anything like this: (1,) or (1,2,3,4,) etc.

The problem is that the first value in this field is not preceded by a ',' so it just ignores them. I still need to know these PKs so I can get an accurate count.

Hopefully this isn't too confusing and someone can assist.

Thanks in advance!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-07 : 15:18:33

This is a great example of why storing more than one value in a field is a bad idea, especially on a column that you want to join to. If at all possible, I suggest that you normalise your data such that these values are split across multiple rows.

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 10:26:58

1 http://www.datamodel.org/NormalizationRules.html
2 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 10:28:48

3 Try


select 
	* 
from Table1 svc inner join 
		Table2 hm ON (','+hm.services+',' like ('%,' + Convert(varchar(2),svc.RecID) + ',%'))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources