LIKE 使用整数，在 SQL 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5754934/
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
LIKE with integers, in SQL
提问by serhio
Can I replacethe =statement with the LIKEone for the integers ?
我可以=用LIKE整数的 语句替换语句吗？
by eg. are the following the same thing:
通过例如。以下是同一件事：
select * from FOOS where FOOID like 2
// and
select * from FOOS where FOOID = 2
I'd prefer to use LIKEinstead of =because I could use %when I have no filter for FOOID...
我更喜欢使用LIKE而不是=因为%当我没有 FOOID 过滤器时我可以使用......
SQL Server 2005.
SQL Server 2005。
EDIT 1 @Martin
编辑 1 @Martin
回答by Martin Smith
select * from FOOS where FOOID like 2
should be avoided as it will cause both sides to be implicitly cast as varcharand mean that an index cannot be used to satisfy the query.
应该避免，因为它会导致双方隐式转换为varchar并意味着索引不能用于满足查询。
CREATE TABLE #FOOS
(
FOOID INT PRIMARY KEY,
Filler CHAR(1000)
)
INSERT INTO #FOOS(FOOID)
SELECT DISTINCT number
FROM master..spt_values
SELECT * FROM #FOOS WHERE FOOID LIKE 2
SELECT * FROM #FOOS WHERE FOOID = 2
DROP TABLE #FOOS
Plans (notice the estimated costs)
计划（注意估计成本）
Another way of seeing the difference in costs is to add SET STATISTICS IO ON
查看成本差异的另一种方法是添加 SET STATISTICS IO ON
You see that the first version returns something like
您会看到第一个版本返回类似
Table '#FOOS__000000000015'. Scan count 1, logical reads 310
The second version returns
第二个版本回归
Table '#FOOS__000000000015'. Scan count 0, logical reads 2
This is beacuse the reads required for the seek on this index are proportional to the index depth whereas the reads required for the scan are proportional to the number of pages in the index. The bigger the table gets the larger the discrepancy between these 2 numbers will become. You can see both of these figures by running the following.
这是因为在此索引上查找所需的读取与索引深度成正比，而扫描所需的读取与索引中的页数成正比。表格越大，这两个数字之间的差异就会越大。您可以通过运行以下命令来查看这两个数字。
SELECT index_depth, page_count
FROM
sys.dm_db_index_physical_stats (2,object_id('tempdb..#FOOS'), DEFAULT,DEFAULT, DEFAULT)
WHERE object_id = object_id('tempdb..#FOOS') /*In case it hasn't been created yet*/
回答by Anthony Faull
Use a CASEstatement to convert an input string to an integer. Convert the wildcard %to a NULL. This will give better performance than implicitly converting the entire int column to string.
使用CASE语句将输入字符串转换为整数。将通配符转换%为NULL. 这将比将整个 int 列隐式转换为字符串提供更好的性能。
CREATE PROCEDURE GetFoos(@fooIdOrWildcard varchar(100))
AS
BEGIN
DECLARE @fooId int
SET @fooId =
CASE
-- Case 1 - Wildcard
WHEN @fooIdOrWildcard = '%'
THEN NULL
-- Case 2 - Integer
WHEN LEN(@fooIdOrWildcard) BETWEEN 1 AND 9
AND @fooIdOrWildcard NOT LIKE '%[^0-9]%'
THEN CAST(@fooIdOrWildcard AS int)
-- Case 3 - Invalid input
ELSE 0
END
SELECT FooId, Name
FROM dbo.Foos
WHERE FooId BETWEEN COALESCE(@fooId, 1) AND COALESCE(@fooId, 2147483647)
END
回答by Quassnoi
Yes, you can just use it:
是的，您可以使用它：
SELECT *
FROM FOOS
WHERE FOOID like 2
or
或者
SELECT *
FROM FOOS
WHERE FOOID like '%'
Integers will be implicitly converted into strings.
整数将被隐式转换为字符串。
Note that neither of these condition is sargable, i. e. able to use an index on fooid. This will always result in a full table scan (or a full index scan on fooid).
请注意，这些条件都不是 sargable，即能够在 上使用索引fooid。这将始终导致全表扫描（或对 的全索引扫描fooid）。
回答by Amir Tofighi
This is a late comment but I thought maybe some other people are looking for the same thing so as I was able to find a solution for this, I thought I should share it here:)
这是一个迟到的评论，但我想也许其他一些人正在寻找同样的东西，所以我能够找到解决方案，我想我应该在这里分享它:)
A short description of the problem: the problem I had was to be able to use the wild card foe integer data types. I am using SQL Server and so my syntax is for SQL Server. I have a column which shows department number and I wanted to pass a variable from my page from a drop down menu. There is an 'All' option as well which in that case I wanted to pass '%' as the parameter. I was using this:
问题的简短描述：我遇到的问题是能够使用通配符敌人整数数据类型。我正在使用 SQL Server，所以我的语法适用于 SQL Server。我有一列显示部门编号，我想从我的页面的下拉菜单中传递一个变量。还有一个 'All' 选项，在这种情况下我想传递 '%' 作为参数。我正在使用这个：
select * from table1 where deptNo Like @DepartmentID
It was working for when I pass a number but not for %because sql server implicitly converts the @DepartmentIDto int (as my deptNo is of type int)
当我传递一个数字时它有效但不是%因为 sql server 隐式转换@DepartmentID为 int （因为我的 deptNo 是 int 类型）
So I casted the deptNo and that fixed the issue:
所以我投了 deptNo 并解决了这个问题：
select * from table1 where CAST(deptNo AS varchar(2)) Like @DepartmentID
This one works for both when I pass a number like 4 and when I pass %.
当我传递像 4 这样的数字和传递%.
回答by BethS
Use NULL as the parameter value instead of % for your wildcard condition
使用 NULL 作为参数值而不是 % 作为通配符条件
select * from table1 where (@DepartmentID IS NULL OR deptNo = @DepartmentID)
