How to split comma (,) separated string and pass to IN clause of select statement

In some cases, we get a comma separated string as output (say from another select statement) that we would need to pass to the IN clause of a select statement.

This article explains how to achieve that using regexp_substr (DB >=10g).

For example, assume a select statement returns the following
'ALLAM,NARASIMHA,JONES,SMITH' 

Now, we would need to pass this to another select statement as IN clause and get the output.
SQL> Select * From EMP where ENAME in ('ALLAM,NARASIMHA,JONES,SMITH' );
       no rows selected 

this is not our expected output. We expect the query to return 4 rows. This can be achieved by splitting the comma separated string to individual strings and pass it to the IN clause. 

Oracle provides regexp_substr() function, which comes handy for this scenario.
 
First, we will form a query, that splits this comma separated string and gives the individual strings as rows.
 
SQL> select regexp_substr('ALLAM,NARASIMHA,JONES,SMITH' ,'[^,]+', 1, level) from dual
          connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.

We can pass this query to our select statement to get the desired output. 

SQL> Select ENAME,EMPNO,ESAL  
          From EMP
          Where ENAME in ( 
                     Select regexp_substr('ALLAM,X,Y,Z,K,N' ,'[^,]+', 1, level) as Names from dual
                     connect by regexp_substr('ALLAM,X,Y,Z,K,N', '[^,]+', 1, level) is not null );

Now, the query returns what we expected.