o7planning

Split comma separated string and pass to IN clause of select statement in Oracle

View more Tutorials:

Websites to learn foreign languages for free:
Follow us on our fanpages to receive notifications every time there are new articles. Facebook Twitter

1- Example

In some situations you have a String, for example:

'KING,JONES,FORD'
You want to turn it into a Select statement and returns results like the illustration below:
With Oracle > 9, you can write SQL:

-- ORACLE > 9.x

 Select Regexp_Substr('KING,JONES,FORD'
                     ,'[^,]+'
                     ,1
                     ,Level) Emp_Name
 From   Dual
 Connect By Regexp_Substr('KING,JONES,FORD'
                         ,'[^,]+'
                         ,1
                         ,Level) Is Not Null;
Find the employees named in a String separated by commas.

Select * From Employee Emp
Where
Emp.Emp_Name In ('KING','JONES','FORD');
You need to combine two nested statement:

Select *
From   Employee Emp
Where  Emp.Emp_Name In
      (Select Regexp_Substr('KING,JONES,FORD'
                           ,'[^,]+'
                           ,1
                           ,Level) Emp_Name
       From   Dual
       Connect By Regexp_Substr('KING,JONES,FORD'
                               ,'[^,]+'
                               ,1
                               ,Level) Is Not Null);
Results of running the statement:

View more Tutorials: