Использование предложения "IN" с строкой с разделителями-запятыми из вывода функции replace() в Oracle SQL

У меня есть строка с разделителями-запятыми, которую я хочу использовать в предложении "IN" инструкции. например: 100, 101, 102

Так как предложение In и "IN" я должен процитировать отдельные строки, я использую функцию replace: например: select '' '' || replace ('100,101,102', ',', '' ',' '') || '' '' из dual;

Приведенный выше запрос работает, однако, когда я пытаюсь использовать вывод вышеприведенного в качестве входа в предложение "IN" , он не возвращает никаких данных. Я ограничен только операторами SQL, поэтому я не могу использовать код PL/SQL. Просьба помочь.

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

Вышеуказанное не работает. Пожалуйста, дайте мне знать, что мне не хватает.

5 ответов

Общий подход в этом случае состоял бы в том, чтобы разобрать список, разделенный запятыми, в коллекцию Oracle и использовать эту коллекцию в вашей инструкции SQL. Том Ките имеет пример этого в своем обсуждении списков переменных IN.

Предполагая, что вы создаете тип myTableType и функцию in_list из этого потока, вы должны иметь возможность делать

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


чистый SQL, но не очень хорошо протестирован...

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: улучшено

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

Примечание:

  • pre/post исправляет ваши строки запятыми
  • принять верхний предел (10 в примере) в соответствии с вашими потребностями (не требуется в улучшенной версии).
  • используйте функцию таблицы in_list, указанную Justing Cave, что, вероятно, лучше:)

кредит: что-то вроде этого в книге Стефана Фарула "Реставрация приложений SQL" (O'Reilly)


Поскольку значения, разделенные запятыми, содержат только цифры, почему бы не попробовать что-то так же просто, как использовать:

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

См. этот пример:

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

Производство:

EMPLOYEE_NUMBER
101
102


Вы можете использовать свой подход с REPLACE и IN, если вы отформатируете весь выбор в виде строки - затем используйте строку с OPEN refcursor FOR или EXECUTE IMMEDIATE.


Вы можете использовать функцию regexp_substr для получения ожидаемого результата. Например ИМЕНА: = 'SMITH, ALLEN, WARD, JONES'; - здесь "NAMES" - это переменная/результат ожидаемого ввода. Это может быть использовано в разделе IN.

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

Вышеупомянутый запрос выполняет итерацию через строку, разделенную запятыми, выполняет поиск запятой (,), а затем разбивает строку, обрабатывая запятую как разделитель. Он возвращает строку как строку, всякий раз, когда она попадает в разделитель. Вот ссылка Нажмите здесь

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

licensed under cc by-sa 3.0 with attribution.