working with a delimited column

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 2000 Forums

SQL Server Development (2000)

working with a delimited column

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-20 : 07:58:22

i havea table with id's and name
and another table which has a colum with a delimited id's in each row
for example : 6541,987154,913647,614874
when i try to do :
select name from table1 where id in (select delimitedcol from table2 where id=5)
it like no 1 of the id's exist in table1 !!!
why can i do this?what can i do so i will get the names?
thnaksi n advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-20 : 08:00:56

Check out following article:

[url]http://www.sommarskog.se/arrays-in-sql.html[/url]

Harsh Athalye
India.
"Nothing is Impossible"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-20 : 08:34:10

this is the solution :
select name
from table1
join table2 on ( table2.id = 5 and charindex ( table1.id, table2.delimitedcol ) > 0 )

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-20 : 09:53:09

quote:
Originally posted by pelegk2

this is the solution :
select name
from table1
join table2 on ( table2.id = 5 and charindex ( table1.id, table2.delimitedcol ) > 0 )

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

This can result in false matches. if the ID is "6" and delimited value is "51,63,82"

Best not to store the values as a delimited string but as seperate related rows. Otherwise you will need a parsing function.

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-20 : 10:03:22

quote:
Originally posted by pelegk2

i havea table with id's and name
and another table which has a colum with a delimited id's in each row
for example : 6541,987154,913647,614874
when i try to do :
select name from table1 where id in (select delimitedcol from table2 where id=5)
it like no 1 of the id's exist in table1 !!!
why can i do this?what can i do so i will get the names?
thnaksi n advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Advice: Normalize your data and store it properly. Never stuff multiple values into a single delimited column in a relational database.

- Jeff

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-25 : 07:40:04

the problem is that i have inherited the data structure like that :(
the fix so there won be a bug like TG said :
select name
from table1
join table2 on ( table2.id = 5 and charindex ( ','+table1.id+',', ','+replace(table2.delimitedcol,' ','')+',' ) > 0 )

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-25 : 09:22:31

Read about Normalisation

http://www.datamodel.org/NormalizationRules.html
http://databases.about.com/od/specificproducts/a/normalization.htm

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-25 : 09:54:53

>>the problem is that i have inherited the data structure like that :(
Sometimes it can be worth adding a new table with normalized data so that you are not changing existing architecture but can take advantage of normalization.

Assuming you won't/can't take the normalization advice:
One problem with your solution is that you will not be able to take advantage of any indexes on table1.[id]. Here is a topic with advice on "parsing" a delimited string to a table varialbe. Also be sure to follow the suggested links on parsing functions.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72452

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources