Compare two list items

Question

I am trying to compare a database field that contains list items (separated by commas), unfortunately a variable, which is also a list item.

Example:

In this case, the user can belong to several groups, and access to the content is also provided to several groups.

contentid | group (1) (c,d) (2) (a,c) (3) (b) 

So, I need to select all the content where the user is in the group (a, c). In this case, contentid 1.2 should be returned.

Answer 1

Here's a safe but slow solution for SQL 2008

 BEGIN -- setup DECLARE @tbl TABLE ( [contentid] INT ,[group] VARCHAR(MAX) ) INSERT INTO @tbl VALUES (1, 'c,d') ,(2, 'a,c') ,(3, 'd') -- send your request as simple xml DECLARE @param XML SET @param = '<g>a</g><g>c</g>' -- query SELECT DISTINCT contentid FROM @tbl t INNER JOIN @param.nodes('/g') AS t2(g) ON ',' + t.[group] + ',' LIKE '%,' + t2.g.value('.', 'varchar(max)') + ',%' END 

You simply pass your request as an XML fragment instead of a comma separated list.

If your group names are single characters, or you can be sure that the names are not subsets of each other's characters (i.e.: GroupA, GroupAB), then the query can be optimized.

 ON t.[group] LIKE '%' + t2.g.value('.', 'varchar(max)') + '%' 

---

If you use RDBMS without the ability to parse XML, you will have to use a string that splits your query into a temporary table and works this way.

---

You really shouldn't use comma separated values ​​inside your columns. It would be much better if the [group] column contained only one value and you had duplicate entries with a UNIQUE constraint for the composite (contentid, group).

Answer 2

You may find this question and answer useful: How to break a string so that I can access element x?

Or you can always use something like this:

 create function SplitString( @string varchar(max), @delimiter char(1) ) returns @items table (item varchar(max)) as begin declare @index int set @index = 0 if (@delimiter is null) set @delimiter = ',' declare @prevdelimiter int set @prevdelimiter = 0 while (@index < len(@string)) begin if (substring(@string, @index, 1) = @delimiter) begin insert into @items select substring(@string, @prevdelimiter, @ index-@prevdelimiter ) set @prevdelimiter = @index + 1 end set @index = @index + 1 end --last item (or only if there were no delimiters) insert into @items select substring(@string, @prevdelimiter, @index - @prevdelimiter + 1) return end go declare @content table(contentid int, [group] varchar(max)) insert into @content select 1, 'c,d' union select 2, 'a,c' union select 3, 'b' declare @groups varchar(max) set @groups = 'a,c' declare @grouptable table(item varchar(max)) insert into @grouptable select * from dbo.SplitString(@groups, ',') select * From @content where (select count(*) from @grouptable g1 join dbo.SplitString([group], ',') g2 on g1.item = g2.item) > 0