Get multiple ids over multiple values ​​in a string with sql?

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 the id and username ie user [2] has id and name

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

+3


source to share


4 answers


Use the function LIKE



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

      

0


source


Try it, it will work for you.



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

      

+1


source


You can strip these comma-separated values ​​using XML functions and then search 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' 

      

+1


source


You shouldn't be storing delimited values ​​in the database, but here's a solution for you that normalizes 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

0


source







All Articles