SQL: expading "in (...)" to "like"

Question

I have sql select as shown below:

select * from table1 where text in (select text from table2) 

In the virtual in clausule it is more difficult to choose this. text is a string (varchar). How to expand this sql to select rows from table1 , where text is like the texts from table2 (not only exactly equal)?

Answer 1

If you have wildcard expressions in the text column in table 2, you can do this as follows.

 select * from Table1 as T1 where exists (select * from Table2 as T2 where T1.[text] like T2.[text]) 

Otherwise, you need to add % to your request.

 select * from Table1 as T1 where exists (select * from Table2 as T2 where T1.[text] like '%'+T2.[text]+'%') 

Answer 2

Something like that:

 select * from table1 where exists ( select * from table2 where table1.text like N'%'+table2.text+N'%' ) 

Note

It could be a performance killer