COMMA Separated Values and IN Operator

How Comma Separated Values can be passed to IN Operator to display result?

Consider a situation where you need to pass comma separated values to IN Operator and display result.

SQL> SELECT * From EMPLOYEES Where ENAME IN('Ravi,Ankit,Pritesh,Ram,Kishna');

no rows selected

We didn’t get the result as expected. To get the Expected result ORACLE provides REGEXP_SUBSTR Function using which comma separated values can be passed.

Let’s check below function will split comma separated values into individual rows.

SQL> SELECT REGEXP_SUBSTR('Ravi,Ankit,Pritesh,Ram,Kishna','[^,]+', 1, Level) From Dual
CONNECT BY REGEXP_SUBSTR('Ravi,Ankit,Pritesh,Ram,Kishna', '[^,]+', 1, Level) IS NOT NULL;

REGEXP_SUBSTR('RAVI,ANKIT,PRI
-----------------------------
Ravi
Ankit
Pritesh
Ram
Kishna

Above Query splits a comma separated string into individual rows whenever it encounters comma (,).

Now, try passing this SELECT Statement to get the desired output from table EMPLOYEES.

SQL> SELECT * From EMPLOYEES Where 
ENAME IN(SELECT REGEXP_SUBSTR('Ravi,Ankit,Pritesh,Ram,Kishna','[^,]+', 1, Level) 
From Dual CONNECT BY REGEXP_SUBSTR('Ravi,Ankit,Pritesh,Ram,Kishna', '[^,]+', 1, Level) IS NOT NULL);

    EMP_NO ENAME          DEPTNO HIREDATE  DNAME      DLOC           SALARY JOB_ID
---------- ---------- ---------- --------- ---------- ---------- ---------- --------------------
      1003 Ankit              30 12-DEC-16 ACCOUNTS                   89000 ACT101
      1005 Pritesh            50 19-AUG-16 ACCOUNTS                   48000 ACT101
      1001 Ravi               10 19-AUG-16 ACCOUNTS                   48000 ACT101
      1021 Ram                20 23-OCT-16 ACCOUNT    BANGALORE      850000 SALES103

Now the Query has return the desired output.

Now consider a situation where you want to pass comma separated values to a Procedure and get the desired output.

Solution 1:

SQL> CREATE OR REPLACE PROCEDURE GetDetails( in_myString in varchar)
  2  AS
  3  Cursor C1 IS
  4  Select ENAME,DNAME,SALARY From EMPLOYEES
  5  Where ENAME in (Select Regexp_Substr(in_myString ,'[^,]+', 1, level) From Dual
  6                       Connect By Regexp_Substr(in_myString, '[^,]+', 1, level) IS Not Null);
  7  Begin
  8  For rec in C1
  9  Loop
 10  DBMS_OUTPUT.PUT_LINE('EMPLOYEE ' ||rec.ENAME ||' Is Associted With ' || rec.DNAME || ' with Salary : ' || rec.SALARY);
 11  End Loop;
 12  End;
 13  /
Procedure created.

Let’s execute above procedure and see if you ger the expected output.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2  GETDETAILS('Ravi,Ankit,Pritesh,Ram,Kishna');
  3  END;
  4  /
EMPLOYEE Ankit Is Associted With ACCOUNTS with Salary : 89000
EMPLOYEE Pritesh Is Associted With ACCOUNTS with Salary : 48000
EMPLOYEE Ravi Is Associted With ACCOUNTS with Salary : 48000
EMPLOYEE Ram Is Associted With ACCOUNT with Salary : 850000

PL/SQL procedure successfully completed.

Solution 2:

SQL> CREATE OR REPLACE PROCEDURE GetDetails(in_myString in Varchar)
  2  As
  3  Cursor C1 IS
  4  Select ENAME,DNAME,SALARY From EMPLOYEES
  5  Where INSTR(in_myString,ENAME)>0;
  6  BEGIN
  7  For rec in C1
  8  Loop
  9  DBMS_OUTPUT.PUT_LINE('EMPLOYEE ' ||rec.ENAME ||' Is Associted With ' || rec.DNAME || ' with Salary : ' || rec.SALARY);
 10  End LOOP;
 11  End;
 12  /

Procedure created.

SQL> BEGIN
  2  GETDETAILS('Ravi,Ankit,Pritesh,Ram,Kishna');
  3  END;
  4  /
EMPLOYEE Ravi Is Associted With ACCOUNTS with Salary : 48000
EMPLOYEE Ankit Is Associted With ACCOUNTS with Salary : 89000
EMPLOYEE Pritesh Is Associted With ACCOUNTS with Salary : 48000
EMPLOYEE Ram Is Associted With ACCOUNT with Salary : 850000

PL/SQL procedure successfully completed.