SQL Server Syntax - Using in or like with comma delimited columns-CodePudding

I have two tables where I want to check if a value exists in the second table. The problem is that in the second table the group field could either be a individual value or a comma delimited value. I need a query to check if the group value exists in table 2 either as a single value or in a comma delimited value

Table 1

Id	Group
1	Group1
2	Group2
3	Group3
5	Group4
6	Group4
7	Group2

Table 2

Group
Group1, Group2
Group3

The results of the select query would be.

Table 1 Filtered Results

Id	Group
1	Group1
2	Group2
3	Group3
7	Group2

CodePudding user response：

Maybe this is what you want:

select * from Table1 t1 where exists (
    select * from Table2 t2 where t2.[Group] like '%' t1.[Group] '%'
)

But be careful!!! "Group" is a keyword in SQL, you should not name a field like that.

UPDATE on Larnu comment:

To avoid Group1 and Group10 match, we can do that:

select * from Table1 t1 where exists (
    select * from Table2 t2 where (t2.[Group] like '%' t1.[Group] or t2.[Group] like '%' t1.[Group] ',%')
)

CodePudding user response：

Try joining the two tables with a LIKE clause, like this:

DECLARE @t1 TABLE (id INT, [group] VARCHAR(255))
DECLARE @t2 TABLE ([group] VARCHAR(255))

INSERT INTO @t1 VALUES (1,'g1'),(2,'g2'),(3,'g3'),(5,'g4'),(6,'g4'),(7,'g2')
INSERT INTO @t2 VALUES ('g1,g2'),('g3')

SELECT DISTINCT t1.* 
FROM @t1 t1 INNER JOIN @t2 t2 
    ON t2.[group] LIKE '%'   t1.[group] OR t2.[group] LIKE '%'   t1.[group]   ',%'

Result:

id	group
1	g1
2	g2
3	g3
7	g2

UPDATED to accommodate Larnu's comment to Carlos's post