oracle how to combine IN and LIKE clause together

There is no direct way to combine IN and LIKE clause in SQL. IN matches keywords and not pattern. One can programmatically combine IN and LIKE by using loops in any programming language.
But sometimes simple SQL is better then PL-SQL block or some other program. In the example below, I am using oracle CASE statement to converge IN and LIKE in simple SQL.

Suppose there are three tables: TABLE_ONE, TABLE_TWO, and TABLE_THREE with the following structure

TABLE_ONE
{
ID,
COL1,
COL2,
COL3
}

TABLE_TWO
{
COL1,
COL2
}

TABLE_THREE
{
COL3
}

Now we want to find records from TABLE_ONE where  TABLE_ONE.COL1 has entries LIKE TABLE_TWO.COL1and TABLE_ONE.COL2 has entries LIKE ABLE_TWO.COL2.

Let's throw some more complexity by adding a requirement that TABLE_ONE.COL3 must have a matching pattern from TABLE_THREE.COL3

Below SQL is using the first requirement in the sub query of CASE statement and generating '1' in the case of match and '0' in the case of a mismatch. Likewise, the second requirement is covered in next CASE sub query.

So case statement generate '1' and '0' against requirements for LIKE. Thereafter this can be used as filters for IN statement in WHERE clause.
 

SELECT * FROM TABLE_ONE WHERE ID IN (
SELECT ID FROM(
select T1.ID,
CASE 
    WHEN 
        EXISTS (SELECT * FROM TABLE_TWO T2 WHERE LOWER(T1.COL1) like '%'||lower(T2.COL1)||'%' OR  T1.COL2 like '%'||T2.COL2||'%') 
        THEN 1
        ELSE 0 
 END AS LIKE_MATCH_1,
 CASE 
    WHEN 
        EXISTS (SELECT * FROM TABLE_THREE T3 WHERE T1.COL3 like '%'||T3.COL1||'%') 
        THEN 1
        ELSE 0 
 END AS LIKE_MATCH_2
from TABLE_ONE T1)
WHERE LIKE_MATCH_1=1 OR LIKE_MATCH_2=1
)