Как проверить на наличие в таблице большого количества значений из списка?

Question

У меня есть список из 1000 номеров.

Как можно найти в таблице все записи, которые имеют номер в списке?

Мой список: (0156556,8787856...)
Ожидаю увидеть:

Name	number
Ольга	0156556
Виктория	8787856

Можно, конечно, сделать:

select name, number 
from base 
where number  in ('0156556','8787856', ...)

Но для большого списка это выглядит не очень эффективно.

Задача усложняется тем, что номера могут быть записаны с дополнительными символами, например:

Name	number
Ольга	01-56-556
Виктория	8787856

И вроде с регулярным выражением все делается просто:

select name, number 
from base 
where  REGEXP_LIKE (number, (0-?1-?5-?6-?5-?5-?6-?)|(8-?7-?8-?7-?8-?5-?6-?))

, но не писать же через OR '|' все 1000 значений.

Answer 1

Ниже воспроизводимый пример самого простого, по моему мнению, решения.

Символьные номера в таблице лучше сразу привести к числам. Добавте виртуальную колонку и индекс по ней:

create table nums (name, num) as
    select 'name '||rownum, trim (column_value) 
    from xmlTable ('"01-56-556","8787856","0156557","87-87-857"')
/
alter table nums add (normnum number invisible as (replace (num, '-'))) 
/
create index idx_nums on nums (normnum)
/

Создайте в директории, доступной для чтения из БД (в примере extfiles -> /tmp/extfiles), текстовый файл импортированный из Excel. Для примера создал его вручную:

$ echo "0156556,8787856"|tr , '\n' >/tmp/extfiles/listnum.dat

Следующий запрос даст ожидаемый результат:

with listnum as (
    select num 
    from external ((num number) 
        type oracle_loader default directory extfiles
        access parameters (records delimited by newline)
        location ('listnum.dat'))) 
select n.name, to_char (n.normnum, '0999999') num
from nums n join listnum l on l.num = n.normnum 
/

NAME     NUM     
-------- --------
name 1    0156556
name 2    8787856

Для версий старше 18c объявление внешних таблиц прямо в SQL запросе (inline) было недоступно. Необходимо создать внешнюю таблицу. Тот же результат, как выше, будет получен:

create table listnum (num number) organization external (
    type oracle_loader default directory extfiles
    access parameters (records delimited by newline)
    location ('listnum.dat'))
/
select n.name, to_char (n.normnum, '0999999') num
from nums n join listnum l on l.num = n.normnum 
/

БЛОГ НА HUSL

Как проверить на наличие в таблице большого количества значений из списка?

Ответы (1 шт):