0

i have a table which contains comma separated values some thing like

id locs
1  a,s,d,f
2  s,d,f,a
3  d,s,a,f
4  d,f,g,a
5  a,s,e
6  f,d

i need out put as 1,2,3,6 in sql server when i have taken comma separated string of id 1.

that means i have taken locs of id 1 and separated with comma, now i want all the ids which contains the separated values of id 1.

Note: I know i don't have to keep comma separated values in table but its happened.

Hope i was clear with my question.

chanti
  • 95
  • 1
  • 9
  • can you tell me exact output – mohan111 Mar 18 '15 at 13:58
  • 2
    Don't do this to yourself. Keep your data normalized and you won't have to fight your data structures. What you have is in violation of 1NF. You will now have to split all your values back out again so you can evaluate them. YUCK!!! http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Mar 18 '15 at 13:59
  • 1
    Why would 4 be excluded from the result, but 5 be included? I can't find an interpretation of "all the ids which contains the separated values of id 1" that would give that result. – Guffa Mar 18 '15 at 14:06
  • @mohan111 out put that i need is the ids which are having the comma separated values of locs from a selected id without the ids that are having other values than these separated values. – chanti Mar 18 '15 at 14:06
  • @SeanLange i know its violation of 1NF but i am new to this project and its a old project so i cant modify now. – chanti Mar 18 '15 at 14:07
  • still i'm unable to get you post the sample output @chanti – mohan111 Mar 18 '15 at 14:07
  • No matter how you slice it you are going to be forced to split those values or do some ugly string manipulation. This has the potential to be unbelievably slow. – Sean Lange Mar 18 '15 at 14:10
  • @mohan111 i need to return the id value of all the rows that has at least one of the comma separated values from the locs column of the row you selected and i don't need the ids which are having other values (even one) than locs column of the row selected. – chanti Mar 19 '15 at 04:57

2 Answers2

0

If I understand you correctly, you need to return the id value of all the rows that has at least one of the comma separated values from the locs column of the row you selected. Since this is a poor database design there can only be an ugly solution to this problem. Start by creating a user defined function to split a comma separated values into a table. there are many ways to do it, this is the first that google found.

DECLARE @Values varchar(max)
SELECT @Values = Locs 
FROM Table WHERE Id = @Id 

SELECT Id
FROM Table INNER JOIN dbo.Split(@Values) SplitedString
ON( '%,'+ SplitedString.s+',%' LIKE ',' + Locs + ',')
Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • you were correct except one thing that is, i don't need the ids which are having other values than locs column of the row selected. from my question if you observe i don't need id 4 as it contains 'g' which is not there in locs of id 1 – chanti Mar 19 '15 at 04:54
  • @chanti but you did say you need id 5 which contains the value 'e'... all the more reason why you should change the database design. – Zohar Peled Mar 19 '15 at 06:34
  • sorry for that i missed it (actually i typed f there) ok now i need are 1,2,3,6 thanks – chanti Mar 19 '15 at 06:38
  • Well, getting the list of rows that contains at least one value was fairly easy. removing from this list the rows that contains values that are not in the list is much harder. you should really reconsider the option of changing this database design. it should be easy to split the values once into a new table, and easy to creata a view that contains the values separated by commas with the name of your current table so that the selects should not be affected. Once the values of the Locs column are properly separated to rows, it should be easy to write a query that will return the ids you need. – Zohar Peled Mar 19 '15 at 07:33
0
declare @tb table (id int, locs varchar(50))
insert into @tb values(1,  'a,s,d,f'),
(2,'s,d,f,a'),
(3,'d,s,a,f'),
(4,'d,f,g,a'),
(5,'a,s,e'),
(6,'f,d')
declare @cta varchar(20)='s,d,f,a'
;with cte0(id,col2)
as
(
select id,t.c.value('.','varchar(max)') as col2 from (select id,x= cast('<t>'+replace(locs,',','</t><t>') +'</t>'  as xml) from @tb) a cross apply x.nodes('/t') t(c)
)
select distinct id from cte0 where  @cta  like '%'+col2+'%' and id not in( select distinct id from cte0 where @cta not like '%'+col2+'%')
nazark
  • 1,240
  • 2
  • 10
  • 15