sql like clause multiple values

Question

I have a table with multiple words, from 1 to n.

declare @words table 
(
    word varchar(100) not null
)

insert into @words (word) values ('word1')
insert into @words (word) values ('word2')
insert into @words (word) values ('word3')

declare @tablea table 
(
    column1 varchar(100) not null
)

insert into @tablea (column1) values ('aword1a aword2a aword3a')
insert into @tablea (column1) values ('word2a')
insert into @tablea (column1) values ('word3a')

Im having trouble to write a query to select from a table where a column is like these words, and I need the AND operator. If the table contains word1, word2, word3, the like clause must match the three words, it means, I want to return the first row in tablea.

select *
from tablea
where
    column1 like ?

Answer 1

Updated:

select t.column1
from @tablea t
inner join @words w on charindex(w.word, t.column1) > 0
group by t.column1 
having count(distinct w.word) = (select count(*) from @words)

Answer 2

Since any one column needs to contain all the values in the @words table, I would use a not exists, and try to find a value in @words that isn't contained in the column1 field.

select
    *
from
    @tablea a
where not exists (
    select 1
    from @words w
    where a.column1 not like '%' + w.word + '%'
)

Answer 3

This will do it, but I'm not sure how extensible it is:

SELECT column1
FROM @tablea t
    JOIN @words w ON t.column1 LIKE '%'+w.word+'%'
GROUP BY column1
HAVING COUNT(*) = (SELECT COUNT(*) FROM @words) 

In the long run, you may be better off implementing Full Text Search.