Code:
SQL>
SQL> --
SQL> -- Check the data in the EMPLOYEES table for a few EMPLOYEE_IDs
SQL> --
SQL> select employee_id, first_name, last_name
2 from employees
3 where employee_id in (100, 121, 145, 179, 202);
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
121 Adam Fripp
145 John Russell
179 Charles Johnson
202 Pat Fay
5 rows selected.
SQL>
SQL> --
SQL> var str varchar2(30)
SQL> exec :str := '100,121,145,179,202';
PL/SQL procedure successfully completed.
SQL> print str
STR
------------------------------
100,121,145,179,202
SQL>
SQL> --
SQL> -- Query 1: Should work in any (non-ancient / real-life) version of Oracle
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
2 from employees e,
3 (
4 select substr(','||:str||',',
5 instr(','||:str||',',',',1,level) + 1,
6 instr(','||:str||',',',',1,level+1) - instr(','||:str||',',',',1,level) - 1
7 ) as token
8 from dual
9 connect by level <= length(:str) - length(replace(:str,',')) + 1
10 ) x
11 where e.employee_id = x.token;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
121 Adam Fripp
145 John Russell
179 Charles Johnson
202 Pat Fay
5 rows selected.
SQL>
SQL> --
SQL> -- Query 2: Should work in Oracle 10g and higher
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
2 from employees e,
3 (
4 select regexp_substr(:str,'[^,]+',1,level) as token
5 from dual
6 connect by level <= length(:str) - length(replace(:str,',')) + 1
7 ) x
8 where e.employee_id = x.token;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
121 Adam Fripp
145 John Russell
179 Charles Johnson
202 Pat Fay
5 rows selected.
SQL>
SQL>
07-28-2014
