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

Question

I have a sql select like below:

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

In real in in clausule is more complex select that this. text is string (varchar). How to expand that sql to select rows from table1 where text is like texts from table2 (not only exactly equals) ?

Answer 1

If you have your wild card expressions in the text column in Table2 you can do like this.

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

Otherwise you need to add the % in your query.

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