SQL服务器使用内部的通配符

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:

既然我认为这应该是一个基本的问题，我知道这个问题可能已经被问过了，但是我找不到。我可能即将获得我的同伴压力徽章，但我还是会问:

Is there a way in SQL Server that I am not aware of for using the wildcard character % when using IN.

在SQL Server中有一种方法，我不知道在使用时使用通配符%。

I realize that I can use OR's like:

我意识到我可以使用或者像:

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?

在本例中，它使用百分号作为字符，而不是通配符，因此不会返回任何行。我现在只是用了一堆或者，当我必须这么做的时候，但是我知道一定有更好的方法。你用什么方法?

13 个解决方案

#1

How about:

如何:

WHERE LEFT(job_no, 4) IN ('0711', '0712', ...)

#2

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

#3

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%')

#4

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 :)

不完全是你想要的，但它有同样的效果，在其他方面也很灵活。

#5

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

#6

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通配符-帮助- 199505721. - html

#7

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

我首先从静态表中添加了一个包含所有通配符结果的选项(这个公司有4个字符的nvarchar代码作为它们的位置，它们通配符是它们的本地属性)，也就是说它们可能有456个?也就是456[1]到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.

插入到测试表中。

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

#8

The IN operator is nothing but a fancy OR of '=' comparisons. In fact it is so 'nothing but' that in SQL 2000 there was a stack overflow bug due to expansion of the IN into 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运算符不过是一个花哨的或“=”的比较。事实上，在SQL 2000中，当列表包含大约10k个条目时(是的，有些人在条目中写了10k个条目…)，由于将In扩展为ORs，导致栈溢出错误。所以不能使用任何通配符匹配。

#9

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*"

从jobdetails中选择*，job_no如“0711*”或job_no如“0712*”

#10

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 NOT keyword you could also use [^1-2]%

如果您只需要0711%和0712%，您还可以在括号内放置一个范围。没有关键字也可以使用[^ 1 - 2]%

#11

-1

You have the answer right there in your question. You cannot directly pass wildcard when using IN. However, you can use a sub-query.

你的问题有答案。在使用IN时不能直接传递通配符。但是，您可以使用子查询。

Try this:

试试这个:

select *
from jobdetails
where job_no in (
select job_no
from jobdetails
where job_no like '0711%' or job_no like '0712%')
)

I know that this looks crazy, as you can just stick to using OR in your WHERE clause. why the subquery? How ever, the subquery approach will be useful when you have to match details from a different source.

我知道这看起来很疯狂，因为你可以坚持使用或在你的WHERE子句中。为什么子查询?无论如何，当您必须匹配来自不同源的详细信息时，子查询方法将非常有用。

Raj

拉吉

#12

-2

Try this

试试这个

select * 
from jobdetails 
where job_no between '0711' and '0713'

the only problem is that job '0713' is going to be returned as well so can use '07299999999999' or just add and job_no <> '0713'

唯一的问题是job '0713'也会被返回所以可以使用' 07299999999999999999999999 '或者添加job_no <> '0713'

Dan zamir

丹水

#13

-2

This might me the most simple solution use like any

这可能是我使用的最简单的解决方案

select *
from jobdetails
where job_no like any ('0711%', '0712%')

In Teradata this works fine.

在Teradata中，这个操作很好。

#1