Using the "IN" Clause with a Comma Separated String from the Output of the replace () Function in Oracle SQL

Question

I have a comma delimited string that I want to use in the "IN" clause of an instruction. e.g. 100, 101, 102

Since the sentence In and "IN" I have to quote the individual lines, I use the replace function: for example: select '' '' || replace ('100,101,102', ',', '' ',' '') || '' '' of dual;

The above query works, however, when I try to use the output of the above as input to the "IN" clause, it does not return any data. I am limited only to SQL statements, so I cannot use PL / SQL code. Please 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

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

Assuming you create the myTableType type and the in_list function from this stream, 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 corrects your lines with commas
• accept the upper limit (10 in the example) according to your needs (not required in the improved version).
• use the in_list table function specified by Justing Cave, which is probably better :)

credit: something like this in Stefan Farul's book Restoring SQL Applications (O'Reilly)

Answer 3

Since comma-separated values ​​contain only numbers, why not try something as easy 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 

Production:

 EMPLOYEE_NUMBER 101 102 

Answer 4

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

Answer 5

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

 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 a line separated by commas, searches for a comma (,), and then breaks the line, treating the comma as a separator. It returns a string as a string, whenever it hits the delimiter.
Here is the link. 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