Pinch Pinch - 3 months ago 7
SQL Question

SQL Joining column is a subset of another

Assuming the following tables:

Table A
[Description]
Some Apples are Green
Many Bananas are Yellow
Carrots are Orange

Table B
[Fruits]
Apples
Bananas
Carrots


I would like to
inner join
these tables where
B.[Fruits]
is a subset of
A.[Description]


Results would be

[Description] [Fruits]
Some Apples are Green Green
Many Bananas are Yellow Yellow
Carrots are Orange Orange

Answer

Demo Here

select dataa,b.colorname from #temp t
cross apply
(select  replace(t.dataa,colorname,'') as rplc,colorname
from #colors)b
where dataa<>rplc