sql server - How SQL do in like?

Question

I'd like do multiple like in query, the logic like below.

E.g:
Table1:

ID
AA001BB
AA002BB
AA003CC
AA004CC
AA004DD
AA006DD

Table2

ID
001
002
004

SQL Query Logic like:

select * from table1 where ID likein (select '%'+ID+'%' from table2)

Get result:

ID
AA001BB
AA002BB
AA004CC
AA004DD

Answer 1

as @Squirrel mention in comment, you can use EXISTS :

SELECT * FROM Table1 t1 WHERE 
EXISTS(SELECT * FROM Table2 t2 WHERE t1.id LIKE ('%' + t2.id + '%'))

here is db<>fiddle for better examine.