Using CONTAINS to find items IN a table

Question

I'm trying to write a SP that will allow users to search on multiple name strings, but supports LIKE functionality. For example, the user's input might be a string 'Scorsese, Kaurismaki, Tarkovsky'. I use a split function to turn that string into a table var, with one column, as follows:

part
------
Scorsese
Kaurismaki
Tarkovsky

Then, normally I would return any values from my table matching any of these values in my table var, with an IN statement:

select * from myTable where lastName IN (select * from @myTableVar)

However, this only returns exact matches, and I need to return partial matches. I'm looking for something like this, but that would actually compile:

select * from myTable where CONTAINS(lastName, select * from @myTableVar)

I've found other questions where it's made clear that you can't combine LIKE and IN, and it's recommended to use CONTAINS. My specific question is, is it possible to combine CONTAINS with a table list of values, as above? If so, what would that syntax look like? If not, any other workarounds to achieve my goal?

I'm using SQL Server 2016, if it makes any difference.

Answer 1

Can your parser built the entire statement? Will that get you what you want?

select * 
from myTable 
where CONTAINS
(lastName, 
'"Scorsese" OR "Kaurismaki" OR "Tarkovsky"'
)

Answer 2

This can be done using CHARINDEX function combined with EXISTS:

select *
from myTable mt
where exists(select 1 from @myTableVar
             where charindex(mt.lastName, part) > 0
                or charindex(part, mt.lastName) > 0)

You might want to omit one of the conditions in the inner query, but I think this is what you want.

Answer 3

You can use EXISTS

SELECT * FROM myTable M
WHERE 
    EXISTS( SELECT * FROM @myTableVar V WHERE M.lastName like '%'+ V.part +'%' )