SQL. Преобразование строки данных с разделителями в список оператора In запроса (Oracle, PostgreSQL).

Зачем это может понадобиться? Например, в приложении пользователь вводит список значений в одну строку, разделитель - запятая. Для известной таблички scott.emp ввод может выглядеть так: SMITH,ALLEN,WARD.

Пример ввода.

А разработчик, соответственно, должен этот ввод отправить в список в in ('SMITH', 'ALLEN', 'WARD') в WHERE какого-то sql-запроса. Т.е. получить на выходе что-то вроде select * from scott.emp where ename in ('SMITH', 'ALLEN', 'WARD')

История невыдуманная, - в Oracle BI Publisher (чтоб его) пользователи так могли параметры передавать.

Заметка навеяна чтением книги "SQL. Сборник рецептов" (Молинаро, де Грааф, 2-е издание). Книга в целом полезная, но есть кое-какие сущностные претензии к содержанию. Такое впечатление, что где-то авторы не стали заморачиваться, и взяли работающие примеры из предыдущего издания. Например, странно для получения Top N значений в выборке предлагать использовать оконную функцию row_number(), когда для каждой из СУБД уже есть решение "из коробки". Fetch first n rows only в IBM DB2 и Оракеле, limit в Постгресе и емнип в MySQL, TOP в сиквел сервере. С другой стороны, для генерации последовательности чисел в Oracle приведено решение через model, а не старый добрый connect by. Собственно, с задачей, которая разбирается в этой заметке, решение из книги тоже ... такое себе.

И это не говоря про чудовищные опечатки. Если бы файлы с примерами кода прилагались, опечатки не были бы большой проблемой. Но, увы.

Итак, сначала будут примеры решения из книги - для Oracle и PostgreSQL.

А в завершении заметки - ещё несколько вариантов решения для Oracle и для PostgreSQL.

Буду писать с минимумом комментариев, скачивание книги легко гуглится.

Дано - строка на входе, например 'SMITH,ALLEN,WARD,JONES'.

Нужно получить select * from scott.emp where ename in ('SMITH', 'ALLEN', 'WARD', 'JONES')

--(1) шаг 1
select emps,pos from (
select ',' || 'SMITH,ALLEN,WARD,JONES' || ',' emps from dual) csv,
  (select rownum pos from scott.emp) iter
where iter.pos <= ((length(csv.emps) - length(replace (csv.emps,',')))/length (',')) -1

Результаты выполнения шага 1

--(2) шаг 2
select 
  substr(emps,
    instr (emps, ',',1,iter.pos) + 1,
    instr (emps, ',',1,iter.pos + 1) - instr (emps, ',',1,iter.pos)
    ) emps
from (select ',' || 'SMITH,ALLEN,WARD,JONES' || ',' emps from dual) csv,
  (select rownum pos from scott.emp) iter
where iter.pos <= ((length(csv.emps) - length(replace (csv.emps,',')))/length (',')) -1

Результаты выполнения шага 2

--(3)финал: отрезаем из предыдущего шага конечную запятую и вставляем в подзапрос  
select * from scott.emp where ename in (
  select rtrim(
      substr(emps,
        instr (emps, ',',1,iter.pos) + 1,
        instr (emps, ',',1,iter.pos + 1) - instr (emps, ',',1,iter.pos)
      ) ,',') — убираем конечную запятую
  from (select ',' || 'SMITH,ALLEN,WARD,JONES' || ',' emps from dual) csv
          ,(select rownum pos from scott.emp) iter
  where iter.pos <= ((length(csv.emps) - length(replace (csv.emps,',')))/length (',')) -1
)

Ура! Получили то, что хотели.

Приведу лишь финальное решение для PostgreSQL, без промежуточных шагов. Кому интересны подробности - могут посмотреть в книжке.

select * from scott.emp e where e.ename  in (
	select
	  split_part (list.vals, ',', iter.pos) as empno
	from (select  generate_series(1,10,1) pos) iter,
	     (select ',' || 'SMITH,ALLEN,WARD,JONES' || ',' as vals) list
	where iter.pos <= length (list.vals) - length (replace(list.vals, ',', ''))
)

Ещё один вариант решения задачи для Oracle

select * from scott.emp where ename in (
 select trim (' ' from trim (chr (10) from trim (chr (13) from regexp_substr ('SMITH,ALLEN,WARD,JONES','[^,]+',1,level))))
                                      from dual connect by level <= regexp_count ('SMITH,ALLEN,WARD,JONES', ',') + 1
)

В группе t.me/oracle_ru комрады подсказали ещё вариант решения для PostgreSQL.

select * 
from scott.emp e 
where ename = any ('{SMITH,ALLEN,WARD,JONES}'::text[])

Оттуда же ещё 1 вариант решения для Oracle:

select trim(column_value) txt
from xmltable(
('"'|| replace('SMITH,ALLEN,WARD,JONES', ',', '","')|| '"'))

И его вариация

select trim(column_value) txt
from  xmltable(
      'ora:tokenize(., ",")'
      passing 'SMITH,ALLEN,WARD,JONES')

И вариант с использованием функции PostgreSQL regexp_split_to_table - на него меня надоумило чтение книжки "SQL для анализа данных" Кэти Танимуры. Книжка познавательная, кстати.

select * from scott.emp e 
where e.ename  in 
  (select regexp_split_to_table('SMITH,ALLEN,WARD,JONES', ','))