Table Of Content
Split comma separated string and pass to IN clause of select statement in Oracle
View more Tutorials:
Follow us on our fanpages to receive notifications every time there are new articles.
Facebook
Twitter
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:
*
