Compare two list items

Question

I am trying to compare a database field which stores list items (comma separated) with unfortunately a variable which is also a list item.

Example:

In this case, a user can belong to multiple groups, and content access is also allocated to multiple groups.

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

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

This is a SQL anti-pattern. The best solution is to normalise your data (one row per content_id | group combination). It's virtually never a good idea to store data as comma separated values. — MatBailie, Oct 22 '12 at 15:46

score 2 · Accepted Answer · answered Oct 22 '12 at 15:53

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 just pass your query in as an XML snippet instead of a comma separated list.

If your group names are single characters or you can be sure the names are not character-subsets of each other (ie: GroupA, GroupAB), then the query can be optimized to.

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

If you're using a RDBMS without XML parsing capability you'll have to use string split your query into a temp table and work it that way.

You really should not be using comma separated values inside your columns. It would be much better if the [group] column only contained one value and you had repeated entries with a UNIQUE constraint on the composite (contentid, group).

score 2 · Answer 2 · edited May 23 '17 at 11:48

You might find this question and answer useful : How do I split a string so I can access item x?

Or you could 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

Compare two list items

2 Answers2