如何编写一个SQL语句来为九个不同的值使用“LIKE”？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

Is there a combination of “LIKE” and “IN” in SQL? 21 answers

SQL中是否有“LIKE”和“IN”的组合? 21个答案

I have to select all the records where:

我必须选择所有记录:

where FIELD2 like '%value21%'
or FIELD2 like '%value22%'
or FIELD2 like '%value23%'
-- repeat up to
or FIELD2 like '%value29%'

Here, value21, ..., value29 are parameters which the user can put in a form before submitting the query. They are (not subsequent) numerical codes, and FIELD2 is a database column holding a string value.

这里,value21,...,value29是用户在提交查询之前可以放入表单的参数。它们是(不是后续的)数字代码,FIELD2是包含字符串值的数据库列。

Which is the most compact form to write down my SQL query?

这是写下我的SQL查询最紧凑的形式?

Note: This is related to an earlier question, but this needs LIKE rather than equals.

注意:这与之前的问题有关,但这需要LIKE而不是equals。

2 个解决方案

#1

I'm afraid you're stuck with:

我担心你会被困在一起:

  WHERE (FIELD2 LIKE '%value21' OR 
         FIELD2 LIKE '%value22' OR 
         FIELD2 LIKE '%value23' ...)

at least in standard SQL (your particular engine might offer some form of full-text indexing that would help).

至少在标准SQL中(您的特定引擎可能提供某种形式的全文索引,这将有所帮助)。

A query like this often indicates a normalization problem in your database design with multiple values stored in a single field value. If that's true in your case and you have any degree of control over the database schema, I advise fixing the problem as soon as possible. Otherwise check your medical coverage to make sure it covers SQL-induced psychosis — this can drive you crazy.

像这样的查询通常表示数据库设计中的规范化问题,其中多个值存储在单个字段值中。如果在您的情况下这是真的,并且您对数据库架构有任何程度的控制,我建议尽快解决问题。否则检查你的医疗保险,以确保它涵盖SQL引起的精神病 - 这可能会让你发疯。

#2

One way:

select Field1, Field2 from Table where Field2 like '%val%'
UNION
select Field1, Field2 from Table where Field2 like '%val2%'
UNION
select Field1, Field2 from Table where Field2 like '%val2%'

etc.

#1