By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tableA
(
ColumnA varchar(max) not null
)
insert into tableA values ('Apple, Ball, Chair, Doll, Egg, Fan, Gun, Hat, Ink, Jug')
1 rows affected
create table tableB
(
ColumnB varchar(max) not null
)
insert into tableB values ('Chair, Ball, Egg, Ink');
1 rows affected
select * from tableA t1
cross apply string_split(t1.ColumnA, ',') t2
where t2.Value in
(select t2.value from tableB t1
cross apply string_split(t1.ColumnB, ',') t2)
| ColumnA | value |
|---|---|
| Apple, Ball, Chair, Doll, Egg, Fan, Gun, Hat, Ink, Jug | Ball |
| Apple, Ball, Chair, Doll, Egg, Fan, Gun, Hat, Ink, Jug | Egg |
| Apple, Ball, Chair, Doll, Egg, Fan, Gun, Hat, Ink, Jug | Ink |