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

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.

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 ) )
    )

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)

As the comma-separated values contain only digits, why not try something as simple as using:

INSTR(','||my_csv_list_of_values||',', ','||my_search_value||',') <> 0

See this example:

-- some test data
with employee as (
  select 101 as employee_number from dual
  union select 200 from dual
  union select 10 from dual
  union select 102 from dual)

-- the actual query
select * from employee
  where INSTR(','||'101,102,103,104'||',', ','||employee_number||',') <> 0;
--                 ^^^^^^^^^^^^^^^^^
--                   your CSV data

Producing:

EMPLOYEE_NUMBER
101
102

You can use your approach with REPLACE and IN if you format the entire select as a string - then use the string with either OPEN refcursor FOR or EXECUTE IMMEDIATE.

You can use XMLTABLE to convert comma separated string to a table.

Example:

select * from employee where employee_number in (
        SELECT  TO_NUMBER (column_value)
        FROM    XMLTABLE('100,101,102') );

You can use regexp_substr function to get expected output.
For Example NAMES:='SMITH,ALLEN,WARD,JONES'; -- here "NAMES" is the variable/result of expected input.that can be use in IN Clause.

SQL> select regexp_substr(NAMES,'[^,]+', 1, level) from dual 2  connect by regexp_substr(NAMES, '[^,]+', 1, level) is not null;


REGEXP_SUBSTR('SMITH,A
----------------------
SMITH
ALLEN
WARD
JONES

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.
Here is an Reference Click Here

 SQL> select * from emp where ename in (
  2  select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
  3  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20