We can clarify your doubts about OBIEE in the "Ask Questions..." Page by explaining your issue.....

Thursday, 25 October 2012

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

Now, we would need to pass this to another select statement as IN clause and get the output.
       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. 

          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.