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.
Try this will work for you
SELECT id,name FROM yourtablename WHERE `users` LIKE '%2%';
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'
Use the LIKE function
SELECT id, name
FROM yourtable
WHERE (user = '2' OR user LIKE '2,%' OR user LIKE '%,2' OR user LIKE '%,2,%')
1,2,3
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