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
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 to share
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 to share