Overview

When you want to provide multiple arguments using LIKE, the method of combining LIKE and IN is syntactically incorrect. Instead of writing lengthy AND, OR conditions, you can write queries using the REGEXP_LIKE function.

Example

select distinct a.table_name from dba_tables a, dba_tab_columns b
where a.table_name=b.table_name
and a.data_type like in ('%LONG%', '%LOB%') -- This is syntactically incorrect
and a.owner='TEST'
order by 1 ; 

Method

Test Scenario

Creating Example Table

SQL> desc TEST.BIG_TABLE

COLUMN_NAME                              TYPE               CONSTRAINT
---------------------------------------- ------------------ --------------------
ID                                       NUMBER             PRIMARY KEY
NAME                                     VARCHAR(100)
DESCRIPTION                              VARCHAR(4000)
CREATED_AT                               DATE
BIG_CLOB                                 CLOB

INDEX_NAME                       TYPE                     COLUMN_NAME
-------------------------------- ------------------------ ----------------------
IDX_BIG_TABLE_NAME               NORMAL                   NAME
_TEST_CON51900895                NORMAL                   ID

You can write queries using AND, LIKE, OR, etc., equivalent to the example query.

SQL> SELECT DISTINCT a.table_name
FROM dba_tables a
JOIN dba_tab_columns b
  ON a.owner = b.owner
 AND a.table_name = b.table_name
WHERE a.owner = 'TEST'
  AND (b.data_type LIKE '%LONG%'
       OR b.data_type LIKE '%LOB%')
ORDER BY 1;


TABLE_NAME
--------------------------------------------------------------------------------
BIG_TABLE

1 row selected.

You can write the query using the REGEXP_LIKE function.

SQL> select distinct a.table_name from dba_tables a, dba_tab_columns b
where a.table_name=b.table_name
and REGEXP_LIKE(b.data_type, 'LOB|LONG')
and a.owner='TEST'
order by 1 ; 


TABLE_NAME
--------------------------------------------------------------------------------
BIG_TABLE

1 row selected.

Using REGEXP_LIKE allows you to express queries much more concisely and with better readability.