TSQL - select rows that contain all items in list-CodePudding

I am provided a list of keywords in the form of comma separated string. I need to look at a column in a database table and return all the rows that this column contains all the keywords in any order. As long as the column contains all the keyword, I will return that row.

For example:

HeroId    HeroName               
--------  -------------------------
1         Ironman              
2         Superman               
3         Spiderman               
4         Otherman

Query string: 'er,man,s'

Expected result: Superman, Spiderman

Explanation: only Superman and Spiderman contain the keywords er, man, and s.

I hope that makes sense.

CodePudding user response：

You can use string_split() and some other logic:

select t.heroid, t.heroname
from t cross apply
     string_split(@query, ',') s
     on t.heroname like concat('%', s.value, '%')
group by t.heroid, t.heroname
having count(distinct s.value) = (select count(distinct ss.value) from string_split(@query, ',') ss);