MySQL 一起使用 SQL LIKE 和 IN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2318126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using SQL LIKE and IN together
提问by Nick
Is there a way to use LIKE and IN together?
有没有办法将 LIKE 和 IN 一起使用？
I want to achieve something like this.
我想实现这样的目标。
SELECT * FROM tablename WHERE column IN ('M510%', 'M615%', 'M515%', 'M612%');
So basically I want to be able to match the column with a bunch of different strings. Is there another way to do this with one query or will I have to loop over the array of strings I am looking for?
所以基本上我希望能够将列与一堆不同的字符串相匹配。有没有另一种方法可以通过一个查询来做到这一点，或者我是否必须遍历我正在寻找的字符串数组？
回答by tvanfosson
How about using a substring with IN.
如何在 IN 中使用子字符串。
select * from tablename where substring(column,1,4) IN ('M510','M615','M515','M612')
回答by paxdiablo
You can do it by in one query by stringing together the individual LIKEs with ORs:
您可以通过将单个 LIKE 与 OR 串在一起，在一个查询中完成此操作：
SELECT * FROM tablename
WHERE column LIKE 'M510%'
OR column LIKE 'M615%'
OR column LIKE 'M515%'
OR column LIKE 'M612%';
Just be aware that things like LIKE and per-row functions don't always scale that well. If your table is likely to grow large, you maywant to consider adding another column to your table to store the first four characters of the field independently.
请注意，像 LIKE 和每行函数这样的东西并不总是能很好地扩展。如果您的表可能会变大，您可能需要考虑向表中添加另一列以独立存储该字段的前四个字符。
This duplicates data but you can guarantee it stays consistent by using insert and update triggers. Then put an index on that new column and your queries become:
这会复制数据，但您可以通过使用插入和更新触发器来保证它保持一致。然后在该新列上放置一个索引，您的查询将变为：
SELECT * FROM tablename WHERE newcolumn IN ('M510','M615','M515','M612');
This moves the cost-of-calculation to the point where it's necessary (when the data changes), notevery single time you read it. In fact, you could go even further and have your new column as a boolean indicating that it was one of the four special types (if that group of specials will change infrequently). Then the query would be an even faster:
这会将计算成本移动到必要的程度（当数据更改时），而不是每次读取它时。事实上，你可以更进一步，将你的新列作为一个布尔值，表明它是四种特殊类型之一（如果这组特价不会经常改变）。然后查询会更快：
SELECT * FROM tablename WHERE is_special = 1;
This tradeoff of storage requirement for speed is a useful trick for larger databases - generally, disk space is cheap, CPU grunt is precious, and data is read far more often than written. By moving the cost-of-calculation to the write stage, you amortise the cost across all the reads.
这种存储需求与速度的权衡对于大型数据库来说是一个有用的技巧——通常，磁盘空间便宜，CPU 消耗量非常宝贵，并且数据的读取频率远高于写入频率。通过将计算成本转移到写入阶段，您可以在所有读取中分摊成本。
回答by Ignacio Vazquez-Abrams
You'll need to use multiple LIKEterms, joined by OR.
您需要使用多个LIKE术语，由OR.
回答by Yada
Use the longer version of IN which is a bunch of OR.
使用较长版本的 IN，它是一堆 OR。
SELECT * FROM tablename
WHERE column LIKE 'M510%'
OR column LIKE 'M615%'
OR column LIKE 'M515%'
OR column LIKE 'M612%';
回答by MissPeri30
substr([column name],
[desired starting position (numeric)],
[# characters to include (numeric)]) in ([complete as usual])
Example
例子
substr([column name],1,4) in ('M510','M615', 'M515', 'M612')
回答by matt
SELECT * FROM tablename
WHERE column IN
(select column from tablename
where column like 'M510%'
or column like 'M615%'
OR column like 'M515%'
or column like'M612%'
)
回答by Rohan
I tried another way
我尝试了另一种方式
Say the table has values
说表有值
1 M510
2 M615
3 M515
4 M612
5 M510MM
6 M615NN
7 M515OO
8 M612PP
9 A
10 B
11 C
12 D
Here cols 1 to 8 are valid while the rest of them are invalid
这里 1 到 8 列是有效的，而其余的则是无效的
SELECT COL_VAL
FROM SO_LIKE_TABLE SLT
WHERE (SELECT DECODE(SUM(CASE
WHEN INSTR(SLT.COL_VAL, COLUMN_VALUE) > 0 THEN
1
ELSE
0
END),
0,
'FALSE',
'TRUE')
FROM TABLE(SYS.DBMS_DEBUG_VC2COLl('M510', 'M615', 'M515', 'M612'))) =
'TRUE'
What I have done is using the INSTR function, I have tried to find is the value in table matches with any of the values as input. In case it does, it will return it's index, i.e. greater than ZERO. In case the table's value does not match with any of the input, then it will return ZERO. This index I have added up, to indicate successful match.
我所做的是使用 INSTR 函数，我试图找到表中的值与作为输入的任何值匹配。如果是，它将返回它的索引，即大于零。如果表的值与任何输入都不匹配，则它将返回零。这个索引是我加起来的，表示匹配成功。
It seems to be working.
它似乎正在工作。
Hope it helps.
希望能帮助到你。
回答by Dustin Buschow
You can use a sub-query with wildcards:
您可以使用带有通配符的子查询：
SELECT 'Valid Expression'
WHERE 'Source Column' LIKE (SELECT '%Column' --FROM TABLE)
Or you can use a single string:
或者您可以使用单个字符串：
SELECT 'Valid Expression'
WHERE 'Source Column' LIKE ('%Source%' + '%Column%')
回答by SimarjeetSingh Panghlia
u can even try this
你甚至可以试试这个
Function
功能
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
Query
询问
select * from my_table inner join (select value from fn_split('M510', 'M615', 'M515', 'M612',','))
as split_table on my_table.column_name like '%'+split_table.value+'%';
回答by Digital Competitive
For a perfectly dynamic solution, this is achievable by combining a cursor and a temp table. With this solution you do not need to know the starting position nor the length, and it is expandable without having to add any OR's to your SQL query.
对于完美的动态解决方案，这可以通过组合游标和临时表来实现。使用此解决方案，您无需知道起始位置或长度，并且无需向 SQL 查询添加任何 OR 即可扩展它。
For this example, let's say you want to select the ID, Details & creation date from a table where a certain list of text is inside 'Details'.
对于此示例，假设您要从“详细信息”中包含特定文本列表的表中选择 ID、详细信息和创建日期。
First create a table FilterTable with the search strings in a column called Search.
首先创建一个表 FilterTable，其中包含名为 Search 的列中的搜索字符串。
As the question starter requested:
正如问题发起者所要求的那样：
insert into [DATABASE].dbo.FilterTable
select 'M510' union
select 'M615' union
select 'M515' union
select 'M612'
Then you can filter your data as following:
然后，您可以按如下方式过滤数据：
DECLARE @DATA NVARCHAR(MAX)
CREATE TABLE #Result (ID uniqueIdentifier, Details nvarchar(MAX), Created datetime)
DECLARE DataCursor CURSOR local forward_only FOR
SELECT '%' + Search + '%'
FROM [DATABASE].dbo.FilterTable
OPEN DataCursor
FETCH NEXT FROM DataCursor INTO @DATA
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #Result
select ID, Details, Created
from [DATABASE].dbo.Table (nolock)
where Details like @DATA
FETCH NEXT FROM DataCursor INTO @DATA
END
CLOSE DataCursor
DEALLOCATE DataCursor
select * from #Result
drop table #Result
Hope this helped
希望这有帮助
