Pass comma separated string inside IN Operator

Published: 12 August 2020
on channel: GoLearningPoint

827

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

Select * From Employee;

Select * From Employee Where NAME IN('Ravi,Akif,Pritesh,Karan');

SELECT REGEXP_SUBSTR('Ravi,Akif,Pritesh,Karan','[^,]+', 1, Level) From Dual
CONNECT BY REGEXP_SUBSTR('Ravi,Akif,Pritesh,Karan', '[^,]+', 1, Level) IS NOT NULL;

SELECT * From EMPLOYEE Where NAME IN
(
SELECT REGEXP_SUBSTR('Ravi,Akif,Pritesh,Karan','[^,+', 1, Level) From Dual
CONNECT BY REGEXP_SUBSTR('Ravi,Akif,Pritesh,Karan', '[^,]+', 1, Level) IS NOT NULL
);

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE GetDetails( in_myString in varchar)
As
Cursor C1 IS
Select NAME,DEPTID,SALARY From EMPLOYEE
Where NAME in (Select Regexp_Substr(in_myString ,'[^,]+', 1, level) From Dual
Connect By Regexp_Substr(in_myString, '[^,]+', 1, level) IS Not Null);
Begin
For rec in C1
Loop
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ' ||rec.NAME ||' Is Associted With ' || rec.DEPTID || ' with Salary : ' || rec.SALARY);
End Loop;
End;

BEGIN
GETDETAILS('RRavi,Akif,Pritesh,Karan');
END;

CREATE OR REPLACE PROCEDURE GetDetails(in_myString in Varchar)
As
Cursor C1 IS
Select NAME,DEPTID,SALARY From EMPLOYEE
Where INSTR(in_myString,NAME) {Greater Than} 0;
BEGIN
For rec in C1
Loop
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ' ||rec.NAME ||' Is Associted With ' || rec.DEPTID || ' with Salary : ' || rec.SALARY);
End LOOP;
End;

Home Page
https://tipsfororacle.blogspot.com/

#GoLearningPoint

On this page of the site you can watch the video online Pass comma separated string inside IN Operator with a duration of hours minute second in good quality, which was uploaded by the user GoLearningPoint 12 August 2020, share the link with friends and acquaintances, this video has already been watched 827 times on youtube and it was liked by 6 viewers. Enjoy your viewing!