Search multiple keyword values in a string

I’ve a table name keywordsTable from where I need to select keywords and need to find the count, how many times they were mentioned in another table named dataTable.

Here’s some dummy data of keywordsTable:

id  userId  context             keywords
-----------------------------------------
1   admin   Hair Products       Shampoo
2   admin   Hair Products       Hair Conditionar
3   admin   Agriculture         Wheat
4   admin   Hair Products       Minoxidil

And here’s some dummy data of dataTable

id     data
------------
1      No matter the event, Sunsilk Black Shine Shampoo with Amla Pearls helps your hair shine upto 24 hrs. So even in heat and sweat it gives your hair upto 24 hrs shine!
2      Choose Head & Shoulders and dandruff will never be a problem again
3      Amazing hair recovery with minoxidil
4      Expansion of agriculture is reducing forest areas

Here’s what I’ve tried so far.

drop table if exists #searchKeywords

select keywords
into #searchKeywords
from keywordTable
where context = 'Hair Products' and userId= 'admin'

select *
from #searchKeywords

select count(*)
from dataTable
where data like ((Select '%'+keywords+'%' from #searchKeywords))

But I’m getting this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer

The like operator doesn’t work in the way you are trying to use it. Instead try a join e.g.

select count(*)
from dataTable T
inner join #searchKeywords K on T.[data] like '%' + K.keywords + '%'