SQL 将“LIKE”与充满字符串的“IN”子句一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5556299/
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 'LIKE' with an 'IN' clause full of strings
提问by John Shedletsky
I want to do a soft string match in a table, like this:
我想在表中进行软字符串匹配，如下所示：
SELECT * FROM emailaddresses where addr in ('[email protected]', '[email protected]')
But if there is an addr value in the table '[email protected]', I want that returned.
但是如果表'[email protected]' 中有一个addr 值，我希望它返回。
Sort of like this:
有点像这样：
SELECT * FROM emailaddresses where addr LIKE in ('[email protected]', '[email protected]')
How do I do that?
我怎么做？
回答by Abe Miessler
put the values into a table and use a joinrather than an inclause:
将值放入表中并使用子句join而不是in子句：
SELECT * FROM emailaddresses as ea
INNER JOIN addresses as a
ON ea.address like '%' + a.address + '%'
回答by Mike Lewis
You can use the LOWER function
您可以使用LOWER 功能
SELECT * FROM emailaddresses where LOWER(addr) in ('[email protected]', '[email protected]')
Which will convert all addr to lowercase, in which you can then compare the results to what you want.
这会将所有 addr 转换为小写，然后您可以将结果与您想要的进行比较。
回答by Cade Roux
Note that LIKEwill work either case-sensitively or case-insensitively depending upon which collation is in effect for the expression, but in your case, you have specified no wildcards so there is little point looking to use LIKE.
请注意，这LIKE将区分大小写或不区分大小写，具体取决于对表达式有效的排序规则，但在您的情况下，您没有指定通配符，因此使用LIKE.
The default SQL Server installation is case-insensitive.
默认的 SQL Server 安装不区分大小写。
If you want a case-insensitive compare because you've got a case-sensitive database, you can cast. I believe this is the appropriate syntax (I've never used it for an IN list on one side of an expression, though).
如果您因为有一个区分大小写的数据库而想要不区分大小写的比较，您可以将. 我相信这是合适的语法（不过，我从未将它用于表达式一侧的 IN 列表）。
SELECT *
FROM emailaddresses
WHERE addr COLLATE SQL_Latin1_General_CP1_CI_AS
IN (
'[email protected]' COLLATE SQL_Latin1_General_CP1_CI_AS
,'[email protected]' COLLATE SQL_Latin1_General_CP1_CI_AS
)
A real case for LIKEwould be something for something like addr LIKE '%@google.com"
一个真实的案例LIKE是类似的东西addr LIKE '%@google.com"
回答by artofsql
Try this using a cross join to a table containing a list of email's you want to search for:
尝试使用交叉连接到包含您要搜索的电子邮件列表的表：
declare @email table(
email_check nvarchar(500) not null)
insert into @email(email_check)
values('[email protected]')
insert into @email(email_check)
values('[email protected]')
select hit, ID, EMAIL_ADDRESS from (
select CHARINDEX(email_check, lower(EMAIL_ADDRESS)) hit, ID, EMAIL_ADDRESS
from Table_With_Email_Addresses
,@email
) t
where hit > 0
No need for a "like" since it will parse a string to find a match. Cheers!
不需要“喜欢”，因为它会解析一个字符串来查找匹配项。干杯!
回答by Prakash Singh
select * from HotelAmenities_
where Pamenities in (
select distinct(pamenities)
from HotelAmenities_
where pamenities like '%Swimming%'
)
回答by Deepak Tekchandani
We can use the 'LIKE-In' approach together in SQL but in somewhat different style, like the one below:
我们可以在 SQL 中一起使用 'LIKE-In' 方法，但风格有些不同，如下所示：
SELECT *
FROM emailaddresses
WHERE addr LIKE '[email protected]' OR addr LIKE '[email protected]'
