SQL Server 在 IN 中使用通配符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1076097/
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
SQL Server using wildcard within IN
提问by Dusty
Since I believe this should be a basic question I know this question has probably been asked, but I am unable to find it. I'm probably about to earn my Peer Pressure badge, but I'll ask anyway:
因为我认为这应该是一个基本问题，所以我知道可能有人问过这个问题，但我找不到它。我可能即将获得 Peer Pressure 徽章，但我还是会问：
Is there a way in SQL Server that I am not aware of for using the wildcard character % when using IN.
在 SQL Server 中是否有一种我不知道在使用 IN 时使用通配符 % 的方法。
I realize that I can use OR's like:
我意识到我可以使用 OR 之类的：
select *
from jobdetails
where job_no like '0711%' or job_no like '0712%'
and in some cases I can use a subquery like:
在某些情况下，我可以使用子查询，例如：
select *
from jobdetails
where job_no in (select job_no from jobs where job_id = 39)
but I'm looking to do something like the following:
但我正在做类似以下的事情：
select *
from jobdetails
where job_no in ('0711%', '0712%')
In this case it uses the percent sign as a character instead of a wildcard character so no rows are returned. I currently just use a bunch of OR's when I have to do this, but I know there has to be a better way. What method do you use for this?
在这种情况下，它使用百分号作为字符而不是通配符，因此不会返回任何行。我目前只在必须这样做时使用一堆 OR，但我知道必须有更好的方法。你用什么方法来做到这一点？
回答by Aaron Alton
How about:
怎么样：
WHERE LEFT(job_no, 4) IN ('0711', '0712', ...)
回答by GalacticCowboy
How about something like this?
这样的事情怎么样？
declare @search table
(
searchString varchar(10)
)
-- add whatever criteria you want...
insert into @search select '0711%' union select '0712%'
select j.*
from jobdetails j
join @search s on j.job_no like s.searchString
回答by notYetExpert
I think I have a solution to what the originator of this inquiry wanted in simple form. It works for me and actually it is the reason I came on here to begin with. I believe just using parentheses around the column like '%text%' in combination with ORs will do it.
我想我有一个简单的形式来解决这个询问的发起者想要什么。它对我有用，实际上这就是我来到这里的原因。我相信只需在列周围使用括号，如 '%text%' 结合ORs 就可以了。
select * from tableName
where (sameColumnName like '%findThis%' or sameColumnName like '%andThis%' or
sameColumnName like '%thisToo%' or sameColumnName like '%andOneMore%')
回答by Jeremy Smyth
You could try something like this:
你可以尝试这样的事情：
select *
from jobdetails
where job_no like '071[12]%'
Not exactly what you're asking, but it has the same effect, and is flexible in other ways too :)
不完全是您要问的，但它具有相同的效果，并且在其他方面也很灵活:)
回答by A-K
SELECT c.* FROM(
SELECT '071235' AS token UNION ALL SELECT '07113'
UNION ALL SELECT '071343'
UNION ALL SELECT '0713SA'
UNION ALL SELECT '071443') AS c
JOIN (
SELECT '0712%' AS pattern UNION ALL SELECT '0711%'
UNION ALL SELECT '071343') AS d
ON c.token LIKE d.pattern
071235
07113
071343
回答by Julian
I had a similar goal - and came to this solution:
我有一个类似的目标 - 并得出了这个解决方案：
select *
from jobdetails as JD
where not exists ( select code from table_of_codes as TC
where JD.job_no like TC.code )
I'm assuming that your various codes ('0711%', '0712%', etc), including the %, are stored in a table, which I'm calling *table_of_codes*, with field code.
我假设您的各种代码（'0711%'、'0712%' 等），包括 %，都存储在一个表中，我称之为 *table_of_codes*，带有字段代码。
If the % is not stored in the table of codes, just concatenate the '%'. For example:
如果 % 未存储在代码表中，只需连接 '%'。例如：
select *
from jobdetails as JD
where not exists ( select code from table_of_codes as TC
where JD.job_no like concat(TC.code, '%') )
The concat() function may vary depending on the particular database, as far as I know.
据我所知， concat() 函数可能因特定数据库而异。
I hope that it helps. I adapted it from:
我希望它有帮助。我改编自：
http://us.generation-nt.com/answer/subquery-wildcards-help-199505721.html
http://us.generation-nt.com/answer/subquery-wildcards-help-199505721.html
回答by Tim
I firstly added one off static table with ALL possibilities of my wildcard results (this company has a 4 character nvarchar code as their localities and they wildcard their locals) i.e. they may have 456? which would give them 456[1] to 456[Z] i.e 0-9 & a-z
I had to write a script to pull the current user (declare them) and pull the masks for the declared user.
Create some temporary tables just basic ones to rank the row numbers for this current user
loop through each result (YOUR Or this Or that etc...)
Insert into the test Table.
我首先添加了一张静态表，其中包含我的通配符结果的所有可能性（这家公司有一个 4 个字符的 nvarchar 代码作为他们的地点，他们通配他们的当地人）即他们可能有 456？这会给他们 456[1] 到 456[Z] 即 0-9 & az
我必须编写一个脚本来拉取当前用户（声明它们）并为声明的用户拉取掩码。
创建一些临时表只是基本的表来对当前用户的行号进行排名
循环遍历每个结果（您的或这个或那个等...）
插入到测试表中。
Here is the script I used:
这是我使用的脚本：
Drop Table #UserMasks
Drop Table #TESTUserMasks
Create Table #TESTUserMasks (
[User] [Int] NOT NULL,
[Mask] [Nvarchar](10) NOT NULL)
Create Table #UserMasks (
[RN] [Int] NOT NULL,
[Mask] [Nvarchar](10) NOT NULL)
DECLARE @User INT
SET @User = 74054
Insert Into #UserMasks
select ROW_NUMBER() OVER ( PARTITION BY ProntoUserID ORDER BY Id DESC) AS RN,
REPLACE(mask,'?','') Mask
from dbo.Access_Masks
where prontouserid = @User
DECLARE @TopFlag INT
SET @TopFlag = 1
WHILE (@TopFlag <=(select COUNT(*) from #UserMasks))
BEGIN
Insert Into #TestUserMasks
select (@User),Code from dbo.MaskArrayLookupTable
where code like (select Mask + '%' from #UserMasks Where RN = @TopFlag)
SET @TopFlag = @TopFlag + 1
END
GO
select * from #TESTUserMasks
回答by Remus Rusanu
The INoperator is nothing but a fancy ORof '=' comparisons. In fact it is so 'nothing but' that in SQL 2000 there was a stack overflow bug due to expansion of the INinto ORs when the list contained about 10k entries (yes, there are people writing 10k IN entries...). So you can't use any wildcard matching in it.
该IN运营商无非是看中了OR“=”比较。事实上，在 SQL 2000 中，当列表包含大约 10k 个条目时（是的，有人写了 10k 个 IN 条目......），由于扩展IN到ORs导致堆栈溢出错误。所以你不能在其中使用任何通配符匹配。
回答by Dan Mihalea
As Jeremy Smith posted it, i'll recap, since I couldn't answer to that particular question of his.
正如杰里米·史密斯 (Jeremy Smith) 发布的那样，我将重述一遍，因为我无法回答他的那个特定问题。
select *
from jobdetails
where job_no like '071[1-2]%'
If you just need 0711%and 0712%you can also place a ranges within the brackets. For the NOTkeyword you could also use [^1-2]%
如果你只是需要0711%，0712%你也可以在括号内放置一个范围。对于NOT关键字，您还可以使用[^1-2]%
回答by PowerUser
In Access SQL, I would use this. I'd imagine that SQLserver has the same syntax.
在 Access SQL 中，我会使用它。我想 SQLserver 具有相同的语法。
select * from jobdetails where job_no like "0711*" or job_no like "0712*"
select * from jobdetails where job_no like "0711*" or job_no like "0712*"
