Using the "IN" clause with a comma delimited string from the output of a replace() function in Oracle SQL
- by Thomas
Hi All,
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.
eg: 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.
