In this article, we are going to see how to use a comma separated
string in IN clause of select statement in Oracle Apex. In some case we will get/construct comma
separated string dynamically that we need to use in IN clause of select
statement.
The following example is done in Oracle Apex. If you want to know more about what is the Oracle apex and its advantages
Click here.
In this article we are going to see the step by step
approach to do it this by using REGEXP_SUBSTR function. In the end of the article, I have added demo application.
For example, assume that the string like this
‘KING,BLAKE,JONES’
The generated comma separated string is a static one then we
can use directly use it in IN clause of select statement. If it is dynamically changes
then we must store that string into one page item(variable). For example the statement
like this.
--------------------------------------------------------------------------------
P4_QUERY:=’KING, BLAKE, JONES’;
Select * from emp where ename in(:P4_QUERY);
--------------------------------------------------------------------------------
I am putting this SQL Statement in IG(Interactive grid).
Well, This will not give the expected output. But we can achieve
this by using the REGEXP_SUBSTR function. We see step by step approach
Step 1: create a page item to hold that comma separated string.
For example P4_QUERY.
Step2: create an IG(Interactive Grid) and paste the
following code.
-----------------------------------------------------------------------------------------------------------------
Select * from emp where ename in (select REGEXP_SUBSTR (:P4_QUERY,'[^,]+',1,LEVEL)
FROM DUAL CONNECT BY LEVEL <= LENGTH (:P4_QUERY)- LENGTH ( REPLACE (:P4_QUERY,
',', ''))+ 1);
-----------------------------------------------------------------------------------------------------------------
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.
Here is the Demo Application for your reference
Click Here
Username: guest
Password: Gpassword
