Using the "IN" clause with a comma delimited string from the output of a replace() function in Oracle SQL

Question

I have an comma delimited string which I want to use in an "IN" clause of the statement. eg: 100,101,102

Since In and "IN" clause I have to quote the individial strings, I use a replace function: eg: select ''''||replace('100,101,102',',',''', ''')||'''' from dual;

The above query works, however, when I try to use the output of the above as an input to the "IN" clause, it returns no data. I am restricted by only SQL statements, so I cannot use PL/SQL code. Kindly help.

select * from employee where employee_number in (
    select ''''||replace('100,101,102',',',''', ''')||'''' from dual);

The above does not work. Please let me know what I am missing.

Answer 1

The general approach in this case would be to parse the comma-separated list into an Oracle collection and to use that collection in your SQL statement. Tom Kyte has an example of this in his discussion on variable IN lists.

Assuming you create the myTableType type and the in_list function from that thread, you should be able to do

SELECT *
  FROM employee
 WHERE employee_number IN (
    SELECT *
      FROM TABLE( in_list( p_your_comma_separated_list ) )
    )

Answer 2

pure SQL, but not very well tested...

select to_number(substr(postfix, 2, instr(postfix, ',' ,2)-2)) id 
  from (
       select substr(val, instr(val, ',', 1, n)) postfix 
         from (select ',101,102,103,' val from dual)
     , (
       select level n
         from dual 
      connect by level < 10) 
  where instr(val, ',', 1, n) > 0)
 where  instr(postfix, ',' ,2)> 2;

EDIT: improved

select substr(postfix, 1, instr(postfix, ',' ,1)-1)
  from (
       select substr(val, instr(val, ',',1, level)+1) postfix
         from (select ',101,102,103,' val from dual)
      connect by instr(val, ',', 2, level) > 0
  );

Note:

• pre/post fix your strings with comma
• adopt the upper limit (10 in the example) as per your needs (not needed in the improved version).
• use the in_list table function mentioned by Justing Cave, that's probably better :)

credit: something like that is in Stephane Faroult's book "Refactorying SQL Applications" (O'Reilly)