SQL 中是否有“LIKE”和“IN”的组合？

Question

提问by selfawaresoup

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.

在 SQL 中，我（遗憾地）经常不得不使用“ LIKE”条件，因为数据库违反了几乎所有规范化规则。我现在无法改变这一点。但这与问题无关。

Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21)for better readability and flexibility of my SQL statements.

此外，我经常使用条件WHERE something in (1,1,2,3,5,8,13,21)来提高我的 SQL 语句的可读性和灵活性。

Is there any possible way to combine these two things without writing complicated sub-selects?

有没有什么可能的方法可以在不编写复杂的子选择的情况下将这两件事结合起来？

I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%')instead of this:

我想要一些简单的东西WHERE something LIKE ('bla%', '%foo%', 'batz%')而不是这样：

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

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

我在这里使用 SQl Server 和 Oracle，但我很感兴趣，如果这在任何 RDBMS 中都是可能的。

Answer 1

采纳答案by OMG Ponies

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

SQL 中没有 LIKE & IN 的组合，在 TSQL (SQL Server) 或 PLSQL (Oracle) 中更不用说。部分原因是全文搜索 (FTS) 是推荐的替代方案。

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle 和 SQL Server FTS 实现都支持 CONTAINS 关键字，但语法仍然略有不同：

Oracle:

甲骨文：

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

SQL 服务器：

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

您正在查询的列必须是全文索引。

Reference:

参考：

Answer 2

回答by Rob van Wijk

If you want to make your statement easily readable, then you can use REGEXP_LIKE (available from Oracle version 10 onwards).

如果您想让您的语句易于阅读，那么您可以使用 REGEXP_LIKE（从 Oracle 版本 10 开始可用）。

An example table:

示例表：

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.

The original syntax:

原始语法：

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.

And a simple looking query with REGEXP_LIKE

和一个带有 REGEXP_LIKE 的简单查询

SQL> select something
  2    from mytable
  3   where regexp_like (something,'^bla|foo|^batz')
  4  /

SOMETH
------
blabla
ofooof
batzzz

3 rows selected.

BUT ...

但 ...

I would not recommend it myself due to the not-so-good performance. I'd stick with the several LIKE predicates. So the examples were just for fun.

由于性能不太好，我不会自己推荐它。我会坚持使用几个 LIKE 谓词。所以这些例子只是为了好玩。

Answer 3

回答by KM.

you're stuck with the

你被困在

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

unless you populate a temp table (include the wild cards in with the data) and join like this:

除非你填充一个临时表（包括数据中的通配符）并像这样加入：

FROM YourTable                y
    INNER JOIN YourTempTable  t On y.something LIKE t.something

try it out (using SQL Server syntax):

尝试一下（使用 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

OUTPUT:

输出：

x
----------
abcdefg
abc

(2 row(s) affected)

x
----------
abc
abcdefg

(2 row(s) affected)

Answer 4

回答by Benoit

With PostgreSQL there is the ANYor ALLform:

PostgreSQL 有ANYorALL形式：

WHERE col LIKE ANY( subselect )

or

或者

WHERE col LIKE ALL( subselect )

where the subselect returns exactly one column of data.

其中子选择只返回一列数据。

Answer 5

回答by mik

Another solution, should work on any RDBMS:

另一个解决方案，应该适用于任何 RDBMS：

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)

Answer 6

回答by Famous Nerd

I would suggest using a TableValue user function if you'd like to encapsulate the Inner Join or temp table techniques shown above. This would allow it to read a bit more clearly.

如果您想封装上面显示的内部连接或临时表技术，我建议使用 TableValue 用户函数。这将允许它更清楚地阅读。

After using the split function defined at: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

使用在以下位置定义的拆分函数后：http: //www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

we can write the following based on a table I created called "Fish" (int id, varchar(50) Name)

我们可以根据我创建的名为“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.

Outputs

输出

1   Bass
2   Pike
7   Angler
8   Walleye

Answer 7

回答by AdaTheDev

One approach would be to store the conditions in a temp table (or table variable in SQL Server) and join to that like this:

一种方法是将条件存储在临时表（或 SQL Server 中的表变量）中，并像这样加入：

SELECT t.SomeField
FROM YourTable t
   JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue

Answer 8

回答by A-K

Use an inner join instead:

改用内部联接：

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

Answer 9

回答by Lukasz Szozda

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

我在这里使用 SQl Server 和 Oracle，但我很感兴趣，如果这在任何 RDBMS 中都是可能的。

Teradata supports LIKE ALL/ANYsyntax:

Teradata 支持LIKE ALL/ANY语法：

ALLevery string in the list.
ANYany string in the list.

┌──────────────────────────────┬────────────────────────────────────┐
│      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%'                    │
└──────────────────────────────┴────────────────────────────────────┘

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%'                    │
└──────────────────────────────┴────────────────────────────────────┘

EDIT:

编辑：

jOOQ version 3.12.0 supports that syntax:

jOOQ 3.12.0 版支持该语法：

Add synthetic [NOT] LIKE ANY and [NOT] LIKE ALL operators

添加合成 [NOT] LIKE ANY 和 [NOT] LIKE ALL 运算符

A lot of times, SQL users would like to be able to combine LIKE and IN predicates, as in:
SELECT *
FROM customer
WHERE last_name [ NOT ] LIKE ANY ('A%', 'E%') [ ESCAPE '!' ]
The workaround is to manually expand the predicate to the equivalent
SELECT *
FROM customer
WHERE last_name LIKE 'A%'
OR last_name LIKE 'E%'
jOOQ could support such a synthetic predicate out of the box.

很多时候，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 可以支持这种开箱即用的合成谓词。

PostgreSQL LIKE/ILIKE ANY (ARRAY[]):

SELECT *
FROM t
WHERE c LIKE ANY (ARRAY['A%', '%B']);

SELECT *
FROM t
WHERE c LIKE ANY ('{"Do%", "%at"}');

db<>fiddle demo

db<>小提琴演示

Snowflake also supports LIKE ANY/LIKE ALLmatching:

Snowflake 还支持LIKE ANY/ LIKE ALL匹配：

LIKE ANY/ALL
Allows case-sensitive matching of strings based on comparison with one or more patterns.
<subject> LIKE ANY (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]

喜欢任何/所有
允许基于与一个或多个模式的比较区分大小写的字符串匹配。
<subject> LIKE ANY (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]

Example:

例子：

SELECT * 
FROM like_example 
WHERE subject LIKE ANY ('%Jo%oe%','T%e')
-- WHERE subject LIKE ALL ('%Jo%oe%','J%e')

Answer 10

回答by mkomo

I have a simple solution, that works in postgresqlat least, using like anyfollowed by the list of regex. Here is an example, looking at identifying some antibiotics in a list:

我有一个简单的解决方案，至少在postgresql中有效，使用like any后跟正则表达式列表。这是一个示例，查看在列表中识别一些抗生素：

select *
from database.table
where lower(drug_name) like any ('{%cillin%,%cyclin%,%xacin%,%mycine%,%cephal%}')

SQL 中是否有“LIKE”和“IN”的组合？

提问by selfawaresoup

采纳答案by OMG Ponies

Oracle:

甲骨文：

SQL Server:

SQL 服务器：

回答by Rob van Wijk

回答by KM.

回答by Benoit

回答by mik

回答by Famous Nerd

回答by AdaTheDev

回答by A-K

回答by Lukasz Szozda

EDIT:

编辑：

回答by mkomo

相关推荐

最近更新

标签

SQL 中是否有“LIKE”和“IN”的组合？

提问by selfawaresoup

采纳答案by OMG Ponies

Oracle:

甲骨文：

SQL Server:

SQL 服务器：

回答by Rob van Wijk

回答by KM.

回答by Benoit

回答by mik

回答by Famous Nerd

回答by AdaTheDev

回答by A-K

回答by Lukasz Szozda

EDIT:

编辑：

回答by mkomo

相关推荐

sql server中的view有什么用？

SQL 外键与连接

SQL DB2 的 Varchar(Max) 等价物是多少？

SQL 从 ORACLE 的 TimeStamp 列中提取时间部分

相关推荐

最近更新

标签