Home > Net >  SQL Server Syntax - Using in or like with comma delimited columns
SQL Server Syntax - Using in or like with comma delimited columns

Time:10-21

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

  • Related