Существует ли комбинация операторов LIKE и IN в условии запроса?

#mysql #sql #sql_server #postgresql #oracle

В SQL (к сожалению) часто приходится пользоваться LIKE из-за того, что в используемых
БД нарушены почти все правила нормализации. Не могу пока это изменить, но это и не
относится к вопросу.

Кроме того, часто использую такое условие WHERE something in (1,1,2,3,5,8,13,21)
в SQL запросах для улучщения их читаемости и расширяемости.

Существует ли возможность объединить эти два оператора без написания сложных подзапросов? 

Хотелось бы нечто такое же простое как: 

WHERE something LIKE ('bla%', '%foo%', 'batz%')` 


вместо такого:

WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'


В основном работаю с SQL Server и Oracle, но будут интересны также решения в других
реляционных СУБД.
    

Ответы

Ответ 1

В Oracle для этого есть оператор REGEXP_LIKE:

select something
from tab
where regexp_like (something, '(^bla|foo|^batz)')
;


Вывод:

SOMETHING 
----------
blaaaa
xxfooo
batzzz


Запрос с оператором OR как в вопросе будет конечно производительней.
Поэтому, если производительность главный критерий, то попробуйте альтернативое решение
с использованием коллекций:

create or replace type strlist is table of varchar2 (4000);
/
select something
from tab
where exists (
    select 1
    from table (strlist ('bla%', '%foo%', 'batz%')) t
    where something like t.column_value
);




Тестовые данные:

create table tab as
with data (something) as (
    select 'blaaaa' from dual union all
    select 'xxfoooo' from dual union all
    select 'batzzz' from dual
) select * from data;


Ответ 2

Такое решение полностью стандартно, работало, и будет работать на любой СУБД:

create table patterns (pattern varchar (32));
insert into patterns values ('bla%');
insert into patterns values ('%foo%');
insert into patterns values ('batz%');

select something
from tab
where exists (
    select 1
    from patterns
    where something like pattern
);


Вывод:

SOMETHING 
----------
blaaaa
xxfooo
batzzz


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


Ответ 3

В SQL Server (если вести речь о комбинировании нескольких LIKE для констант-литералов)
стоит сравнить комбинирование через OR с Left Semi Join альтернативой

SELECT t.something
FROM tab t
WHERE EXISTS (
    SELECT *
    FROM (VALUES ('bla%'), ('%foo%'), ('batz%'))p (pattern)
    WHERE t.something LIKE p.pattern
    );


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

Есть ещё вариант с использованием функции STRING_SPLIT (начиная с SQL Server 2016)

SELECT t.something
FROM tab t
WHERE EXISTS (
    SELECT *
    FROM STRING_SPLIT('bla%|%foo%|batz%', '|') spl
    WHERE t.something LIKE spl.value
    )


Синтаксически и функционально схож с альтернативой на основе VALUES, но менее производителен
и обладает дополнительными ограничениями (из-за символа разделителя).

Однако, если ставить целью лаконичность кода при дальнейшем использовании, то с помощью
STRING_SPLIT можно создать функцию-обёртку, в которую можно поместить WHERE EXISTS
(для применения к таблицам лучше использовать встраиваемую табличную функцию)

CREATE FUNCTION dbo.LikeAny
(
    @value nvarchar(4000),
    @likePatterns nvarchar(4000)
)
RETURNS TABLE
AS
RETURN
    SELECT CAST(1 as bit) AS IsLikeAny
    WHERE EXISTS (
        SELECT *
        FROM STRING_SPLIT(@likePatterns, '|') spl
        WHERE @value like spl.value
        )
GO


С таблицей потом использовать так

SELECT t.something
FROM tab t
    CROSS APPLY dbo.LikeAny(t.something, 'bla%|%foo%|batz%') la


Для скалярного применения удобнее будет скалярная функция

CREATE FUNCTION dbo.LikeAnyS
(
    @value nvarchar(4000),
    @likePatterns nvarchar(4000)
)
RETURNS bit
AS
BEGIN
    RETURN IIF(
        EXISTS (
        SELECT *
        FROM STRING_SPLIT(@likePatterns, '|') spl
        WHERE @value like spl.value
        ), 1, 0);
END
GO


Использовать можно так

DECLARE @string nvarchar(100);
SET @string = 'aafooaa';
SELECT dbo.LikeAnyS(@string, 'bla%|%foo%|batz%');


Встроенного REGEXP функционала в SQL Server нет, но можно его добавить с помощью
CLR-функции, которая по производительности, вероятно, позволит приблизиться к OR-варианту,
а по лаконичности - к REGEXP_LIKE-варианту в Oracle

SELECT something
FROM tab
WHERE CLR.RxLike(something, '(^bla|foo|^batz)') = 1;


Если на столбце есть полнотекстовый индекс, то вместо LIKE 'bla%' и LIKE 'batz%'
можно воспользоваться функцией CONTAINS

SELECT something
FROM tab
WHERE CONTAINS(something, '"bla*" or "batz*")');




Для LIKE '%foo%' в SQL Server нельзя построить эквивалент с помощью CONTAINS, т.к.
LIKE найдёт foo в любой части строки, но CONTAINS так искать не может, CONTAINS может
найти слово (CONTAINS(.., 'foo')) или начало слова (CONTAINS(.., '"foo*"')), или словоформы
(CONTAINS(.., 'FORMSOF (INFLECTIONAL, foo)')). 

В этом смысле, на мой взгляд, LIKE всё-таки ближе к REGEX по функциональности, чем
к полнотекстовому поиску.


Ответ 4

Нет, комбинации операторов LIKE и IN в стндарте SQL не существует, и вряд ли она
появится.

В основном причина кроется в том, что поиск по LIKE паттерну, даже если колонка индексирована
стандартным B-TREE индексом, может быть крайне неэффективным в плане производительности. 

Рекомендованная альтернатива - FTS (full text search) с оператором CONTAINS. Многие
СУБД уже включили поддержку FTS, хотя синтаксис может несколько отличаться. 

Пример для Oracle

Для тестовых данных необходимо создать индекс с типом CONTEXT:

create index idx_something on tab (something) indextype is ctxsys.context; 


И такой запрос:

select something 
from tab t
where contains (t.something, 'bla% or %foo% or batz%') > 0;    


Даст желаемый результат:

SOMETHING 
----------
blaaaa
xxfooo
batzzz


Подробнее см.: Oracle Text SQL Statements and Operators.