Get multiple id on multiple value in string with sql?

Question

I have one table like

tbl

---------------------
id  users   name
---------------------
1   2,3     acc1
2   4       acc2
3   2,4,1   acc3
4   4,1     acc4

In this table I want to get id and name by users i.e user [2] have which id and name

Suppose I pass user [2] then i get result id is 1 and 3 and name acc1 and acc3.

this maybe something similar that you need http://stackoverflow.com/questions/5096630/how-to-split-string-using-delimiter-char-using-t-sql — Yu Yenkan, May 12 '15 at 09:04

score 1 · Answer 1 · answered May 12 '15 at 08:59

1

Try this will work for you

    SELECT id,name FROM yourtablename WHERE `users` LIKE '%2%';

answered May 12 '15 at 08:59

Deepak Keynes

311
2
16

For the usecase, where "users" column have value 20, 200, etc. Your SQL will fail the test case. – abhijitcaps May 12 '15 at 09:00

score 1 · Answer 2 · answered May 12 '15 at 09:08

You can split those comma separated values using XML functions and then search in the result :

DECLARE @table TABLE(id  INT, users   VARCHAR(30), name VARCHAR(30))
INSERT INTO @table VALUES
(1,'2,3','acc1'),
(2,'4','acc2'),
(3,'2,4,1','acc3'),
(4,'4,1','acc4')


SELECT t.id,
       t.name,
       ( c1.value('.', 'varchar(100)') )
FROM   (SELECT id,
               name,
               CAST('<N>' + REPLACE(users, ',', '</N><N>') + '</N>' AS XML)
        FROM   @table) t(id, name, c)
       CROSS APPLY c.nodes('/N') AS t1(c1)
WHERE  ( c1.value('.', 'varchar(100)') ) = '2'

Matt · Accepted Answer · 2015-05-12T09:07:58.373

0

Use the LIKE function

SELECT id, name
FROM yourtable
WHERE (user = '2' OR user LIKE '2,%' OR user LIKE '%,2' OR user LIKE '%,2,%')

edited May 12 '15 at 09:07

answered May 12 '15 at 08:50

Matt

14,906
27
99
149

what if, data 200 present in users instead of 2,3? – knkarthick24 May 12 '15 at 08:53
@Matt this is no longer needed (OR user LIKE '%,2,%') – MAC May 12 '15 at 08:59
@MAC it is if user is `1,2,3` – Matt May 12 '15 at 09:00
the (user LIKE '%2,%') or (user LIKE '%,2%') will do @Matt it will still give the result of 2 even when it is in the middle – MAC May 12 '15 at 09:01
@MAC No that wont work as that could include `32` or `200` for example and wouldnt include where `2` is in the middle of two numbers like `1,2,3` for example – Matt May 12 '15 at 09:02
yeah you're right...so the entire query is wrong since the query is using OR; therefore when one is true, it will still give the result...so if the user is 1,23,52,200 it will give the result of 23, 52 and 200 @Matt – MAC May 12 '15 at 09:05
@MAC i should put it in brackets – Matt May 12 '15 at 09:07
1

This is a bad solution. The correct method is to split the values down first. The values shouldn't be stored as a delimited string in the first place for this very reason. See my answer. – John Bell May 12 '15 at 09:09

score 0 · Answer 4 · answered May 12 '15 at 09:03

You shouldn't store delimited values in a database, but here's a solution for you, that will normalise the data:

;WITH CTE AS (
SELECT T1.[id], T2.my_Splits AS [user], T1.[name]
FROM (
  SELECT *,CAST('<X>'+replace(T.users,',','</X><X>')+'</X>' as XML) as my_Xml 
  FROM Table1 T
 ) T1
 CROSS APPLY ( 
 SELECT my_Data.D.value('.','varchar(50)') as my_Splits
 FROM T1.my_Xml.nodes('X') as my_Data(D)) T2)
SELECT *
FROM CTE
WHERE [user] = 2

And a working fiddle: http://sqlfiddle.com/#!6/dcec6/1

Get multiple id on multiple value in string with sql?

4 Answers4