В 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 здесь, но мне интересно, возможно ли это в любой СУБД вообще.
В SQL нет комбинации LIKE и IN, а тем более в TSQL (SQL Server) или PLSQL (Oracle). Частично это объясняется тем, что рекомендуется использовать полнотекстовый поиск (FTS).
Обе реализации Oracle и SQL Server FTS поддерживают ключевое слово CONTAINS, но синтаксис все еще немного отличается:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
Ссылка:
Если вы хотите сделать выражение легко читаемым, вы можете использовать REGEXP_LIKE (доступный от Oracle версии 10 и далее).
Пример таблицы:
SQL> create table mytable (something)
2 as
3 select 'blabla' from dual union all
4 select 'notbla' from dual union all
5 select 'ofooof' from dual union all
6 select 'ofofof' from dual union all
7 select 'batzzz' from dual
8 /
Table created.
Исходный синтаксис:
SQL> select something
2 from mytable
3 where something like 'bla%'
4 or something like '%foo%'
5 or something like 'batz%'
6 /
SOMETH
------
blabla
ofooof
batzzz
3 rows selected.
И простой запрос с REGEXP_LIKE
SQL> select something
2 from mytable
3 where regexp_like (something,'^bla|foo|^batz')
4 /
SOMETH
------
blabla
ofooof
batzzz
3 rows selected.
НО...
Я бы не рекомендовал его сам из-за не очень хорошей работы. Я бы придерживался нескольких предикатов LIKE. Таким образом, примеры были просто для удовольствия.
вы застряли с
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
если вы не заселяете таблицу temp (включая дикие карты с данными) и присоединяетесь так:
FROM YourTable y
INNER JOIN YourTempTable t On y.something LIKE t.something
попробуйте (используя синтаксис SQL Server):
declare @x table (x varchar(10))
declare @y table (y varchar(10))
insert @x values ('abcdefg')
insert @x values ('abc')
insert @x values ('mnop')
insert @y values ('%abc%')
insert @y values ('%b%')
select distinct *
FROM @x x
WHERE x.x LIKE '%abc%'
or x.x LIKE '%b%'
select distinct x.*
FROM @x x
INNER JOIN @y y On x.x LIKE y.y
ВЫВОД:
x
----------
abcdefg
abc
(2 row(s) affected)
x
----------
abc
abcdefg
(2 row(s) affected)
В PostgreSQL есть форма ANY или ALL:
WHERE col LIKE ANY( subselect )
или
WHERE col LIKE ALL( subselect )
где подзаголовок возвращает ровно один столбец данных.
Другое решение должно работать на любой СУБД:
WHERE EXISTS (SELECT 1
FROM (SELECT 'bla%' pattern FROM dual UNION ALL
SELECT '%foo%' FROM dual UNION ALL
SELECT 'batz%' FROM dual)
WHERE something LIKE pattern)
Я бы предложил использовать функцию пользователя TableValue, если вы хотите инкапсулировать методы Inner Join или temp table, показанные выше. Это позволит ему читать более четко.
После использования функции split, определенной по адресу: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
мы можем написать следующее на основе созданной мной таблицы "Fish" (int id, varchar (50) Name)
SELECT Fish.* from Fish
JOIN dbo.Split('%ass,%e%',',') as Splits
on Name like Splits.items //items is the name of the output column from the split function.
Выходы
1 Bass 2 Pike 7 Angler 8 Walleye
Один из подходов - хранить условия в таблице temp (или табличной переменной в SQL Server) и присоединяться к такому:
SELECT t.SomeField
FROM YourTable t
JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue
Вместо этого используйте внутреннее соединение:
SELECT ...
FROM SomeTable
JOIN
(SELECT 'bla%' AS Pattern
UNION ALL SELECT '%foo%'
UNION ALL SELECT 'batz%'
UNION ALL SELECT 'abc'
) AS Patterns
ON SomeTable.SomeColumn LIKE Patterns.Pattern
u может даже попробовать это
Функция
CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
Запрос
select * from my_table inner join (select value from fn_split('ABC,MOP',','))
as split_table on my_table.column_name like '%'+split_table.value+'%';
Мне тоже было интересно, что-то в этом роде. Я просто тестировал, используя комбинацию SUBSTRING и IN, и это эффективное решение для такого рода проблем. Попробуйте выполнить следующий запрос:
Select * from TB_YOUR T1 Where SUBSTRING(T1.Something, 1,3) IN ('bla', 'foo', 'batz')
У меня есть простое решение, которое работает в postgresql, используя like any, за которым следует список регулярных выражений. Вот пример, рассматривающий выявление некоторых антибиотиков в списке:
select *
from database.table
where lower(drug_name) like any ('{%cillin%,%cyclin%,%xacin%,%mycine%,%cephal%}')
У меня может быть решение для этого, хотя оно будет работать только в SQL Server 2008, насколько я знаю. Я обнаружил, что вы можете использовать конструктор строк, описанный в fooobar.com/questions/30047/..., чтобы присоединиться к таблице "вымышленный", используя аналогичное предложение. Это звучит сложнее, чем есть, посмотрите:
SELECT [name]
,[userID]
,[name]
,[town]
,[email]
FROM usr
join (values ('hotmail'),('gmail'),('live')) as myTable(myColumn) on email like '%'+myTable.myColumn+'%'
Это приведет к тому, что все пользователи будут иметь адрес электронной почты, подобный тем, которые указаны в списке. Надеюсь, что это полезно для всех. Эта проблема беспокоила меня некоторое время.
Для Sql Server вы можете обратиться к Dynamic SQL.
В большинстве случаев в таких ситуациях у вас есть параметр предложения IN на основе некоторых данных из базы данных.
Пример ниже немного "принудительный", но это может соответствовать различным реальным случаям, найденным в старых базах данных.
Предположим, что у вас есть таблица Лица, где имена людей хранятся в одном поле Имя_пользователя как FirstName + '' + LastName. Вам нужно выбрать всех людей из списка первых имен, сохраненных в поле NameToSelect в таблице NamesToSelect, а также некоторые дополнительные критерии (например, отфильтрованные по полу, дате рождения и т.д.),
Вы можете сделать это следующим образом
-- @gender is nchar(1), @birthDate is date
declare
@sql nvarchar(MAX),
@subWhere nvarchar(MAX)
@params nvarchar(MAX)
-- prepare the where sub-clause to cover LIKE IN (...)
-- it will actually generate where clause PersonName Like 'param1%' or PersonName Like 'param2%' or ...
set @subWhere = STUFF(
(
SELECT ' OR PersonName like ''' + [NameToSelect] + '%'''
FROM [NamesToSelect] t FOR XML PATH('')
), 1, 4, '')
-- create the dynamic SQL
set @sql ='select
PersonName
,Gender
,BirstDate -- and other field here
from [Persons]
where
Gender = @gender
AND BirthDate = @birthDate
AND (' + @subWhere + ')'
set @params = ' @gender nchar(1),
@birthDate Date'
EXECUTE sp_executesql @sql, @params,
@gender,
@birthDate
В Oracle вы можете использовать коллекцию следующим образом:
WHERE EXISTS (SELECT 1
FROM TABLE(ku$_vcnt('bla%', '%foo%', 'batz%'))
WHERE something LIKE column_value)
Здесь я использовал предопределенный тип коллекции ku$_vcnt, но вы можете объявить свой собственный следующим образом:
CREATE TYPE my_collection AS TABLE OF VARCHAR2(4000);
Я работаю здесь с SQl Server и Oracle, но мне интересно, возможно ли это вообще в любой СУБД.
Teradata поддерживает синтаксис LIKE ALL/ANY:
ВСЕ каждая строка в списке.
ЛЮБАЯ любая строка в списке.┌──────────────────────────────┬────────────────────────────────────┐ │ THIS expression … │ IS equivalent to this expression … │ ├──────────────────────────────┼────────────────────────────────────┤ │ x LIKE ALL ('A%','%B','%C%') │ x LIKE 'A%' │ │ │ AND x LIKE '%B' │ │ │ AND x LIKE '%C%' │ │ │ │ │ x LIKE ANY ('A%','%B','%C%') │ x LIKE 'A%' │ │ │ OR x LIKE '%B' │ │ │ OR x LIKE '%C%' │ └──────────────────────────────┴────────────────────────────────────┘
JOOQ версии 3.12.0 поддерживает этот синтаксис:
Добавить синтетические [NOT] LIKE ANY и [NOT] LIKE ALL операторы
Часто пользователи SQL хотели бы иметь возможность комбинировать предикаты LIKE и IN, например:
SELECT * FROM customer WHERE last_name [ NOT ] LIKE ANY ('A%', 'E%') [ ESCAPE '!' ]Обходной путь - вручную расширить предикат до эквивалентного
SELECT * FROM customer WHERE last_name LIKE 'A%' OR last_name LIKE 'E%'JOOQ может поддерживать такой синтетический предикат из коробки.
Если вы используете MySQL, ближе всего вы можете получить полнотекстовый поиск:
Это работает для значений, разделенных запятыми
DECLARE @ARC_CHECKNUM VARCHAR(MAX)
SET @ARC_CHECKNUM = 'ABC,135,MED,ASFSDFSF,AXX'
SELECT ' AND (a.arc_checknum LIKE ''%' + REPLACE(@arc_checknum,',','%'' OR a.arc_checknum LIKE ''%') + '%'')''
Вычисляет:
AND (a.arc_checknum LIKE '%ABC%' OR a.arc_checknum LIKE '%135%' OR a.arc_checknum LIKE '%MED%' OR a.arc_checknum LIKE '%ASFSDFSF%' OR a.arc_checknum LIKE '%AXX%')
Если вы хотите использовать индексы, вы должны опустить первый символ '%'.
В Oracle RBDMS вы можете добиться этого, используя REGEXP_LIKE.
Следующий код проверяет, присутствует ли строка три в выражении списка один | два | три | четыре | пять (в котором труба " |" означает ИЛИ логическую операцию).
SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('three', 'one|two|three|four|five');
RESULT
---------------------------------
Success !!!
1 row selected.
Предшествующее выражение эквивалентно:
three=one OR three=two OR three=three OR three=four OR three=five
Итак, это сработает.
С другой стороны, следующий тест не будет выполнен.
SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('ten', 'one|two|three|four|five');
no rows selected
Существует несколько функций, связанных с регулярными выражениями (REGEXP_ *), доступными в Oracle с версии 10g. Если вы разработчик Oracle и интересуетесь этой темой, это должно быть хорошим началом Использование регулярных выражений с Oracle Database.
Нет ответа:
SELECT * FROM table WHERE something LIKE ('bla% %foo% batz%')
В оракуле нет проблем.
Начиная с 2016 года, SQL Server включает в себя STRING_SPLIT функцию. Я использую SQL Server v17.4, и я получил это для работы для меня:
DECLARE @dashboard nvarchar(50)
SET @dashboard = 'P1%,P7%'
SELECT * from Project p
JOIN STRING_SPLIT(@dashboard, ',') AS sp ON p.ProjectNumber LIKE sp.value
В Teradata вы можете использовать LIKE ANY ('%ABC%','%PQR%','%XYZ%'). Ниже приведен пример, который дал мне те же результаты
--===========
-- CHECK ONE
--===========
SELECT *
FROM Random_Table A
WHERE (Lower(A.TRAN_1_DSC) LIKE ('%american%express%centurion%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%bofi%federal%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%american%express%bank%fsb%'))
;
--===========
-- CHECK TWO
--===========
SELECT *
FROM Random_Table A
WHERE Lower(A.TRAN_1_DSC) LIKE ANY
('%american%express%centurion%bank%',
'%bofi%federal%bank%',
'%american%express%bank%fsb%')
Может быть, вы думаете, комбинация, как это:
SELECT *
FROM table t INNER JOIN
(
SELECT * FROM (VALUES('bla'),('foo'),('batz')) AS list(col)
) l ON t.column LIKE '%'+l.Col+'%'
Если вы определили полнотекстовый индекс для вашей целевой таблицы, вы можете использовать эту альтернативу:
SELECT *
FROM table t
WHERE CONTAINS(t.column, '"bla*" OR "foo*" OR "batz*"')
сделайте это
WHERE something + '%' in ('bla', 'foo', 'batz')
OR '%' + something + '%' in ('tra', 'la', 'la')
или
WHERE something + '%' in (select col from table where ....)