LIKE 子句在 SQL Server 中不起作用

发布于 2024-10-31 05:13:41 字数 420 浏览 1 评论 0原文

我有一个表，其中包含 SQL Server 2005 中的以下列：

DocID   NSN  QTY
-----------------
DHA12    32    5
DSRB23   22   45
TF22     70   23

每当我执行以下查询来获取所有具有以 DSRB 或 DHA 开头的 DocId 的 NSN 时，它都会给我空集：

SELECT  DocId, NSN, Qty, RequestDate, ReceiveDate
  FROM  Orders 
 WHERE (DocID LIKE '%DSRB%') 
   AND (DocID LIKE '%DHA%')

我猜想有两个问题LIKE 子句，因为当我删除最后一个 LIKE 子句时，查询工作正常。

原文

I have a table which have the following columns in SQL Server 2005:

DocID   NSN  QTY
-----------------
DHA12    32    5
DSRB23   22   45
TF22     70   23

Whenever I perform the following query to get all the NSN which have a DocId that starts with either DSRB or DHA, it gives me empty set:

SELECT  DocId, NSN, Qty, RequestDate, ReceiveDate
  FROM  Orders 
 WHERE (DocID LIKE '%DSRB%') 
   AND (DocID LIKE '%DHA%')

I guess the problem with having two LIKE clauses because when I delete the last LIKE clause, the query works fine.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

东北女汉子 2024-11-07 05:13:41

您得到空结果，因为表中没有 DocID 类似于 both %DSRB% and 的行%DHA% 同时。您在问题中没有说，但我猜您希望收到 DocIds DHA12 和 DSRB23 的两行。

为此，您需要选择 DocID 类似于任一 %DSRB% 或 % 的行DHA%。尝试将 WHERE 子句中的 AND 更改为 OR：

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') OR (DocID LIKE '%DHA%')

请参阅此 SQL 逻辑运算符简介如果您需要更多示例。

You get an empty result because there is no row in your table where DocID is like both %DSRB% and %DHA% at the same time. You don't say in your question, but I guess you are expecting to receive the two rows with DocIds DHA12 and DSRB23.

To do this, you need to select rows where DocID is like either %DSRB% or %DHA%. Try changing the AND in your WHERE clause to an OR:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') OR (DocID LIKE '%DHA%')

See this introduction to SQL Logical Operators if you want more examples.

回复收藏 0 原文

桃扇骨 2024-11-07 05:13:41

如果要查找包含 XX 或 YY 的行，则需要使用或：

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate 
FROM Orders 
WHERE (DocID LIKE '%DSRB%')
   OR (DocID LIKE '%DHA%')

If you want to find rows that contain either XX or YY, you need to use OR:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate 
FROM Orders 
WHERE (DocID LIKE '%DSRB%')
   OR (DocID LIKE '%DHA%')

回复收藏 0 原文

喜你已久 2024-11-07 05:13:41

您不想在其中使用 OR 而不是 AND 吗？

回复收藏 0 原文

尴尬癌患者 2024-11-07 05:13:41

表中没有包含字符串“DSRB”且包含字符串“DHA”的行。

回复收藏 0 原文

一江春梦 2024-11-07 05:13:41

将“AND”改为“OR”。此时，您说“WHERE”中的两个子句都必须为真。

回复收藏 0 原文

九局 2024-11-07 05:13:41

您确定需要 AND 吗？您没有显示满足这些要求的记录。我想你可能想要 OR，它应该返回两条记录。

回复收藏 0 原文

几度春秋 2024-11-07 05:13:41

您夹在中间的 LIKE 是表扫描，因此性能同样糟糕（或更差）的替代方案是

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE REPLACE(REPLACE(DocID, 'DSRB', ''), 'DHA', '') != DocID

Your sandwiched LIKE is a table scan, so an alternative that performs just as badly (or worse) would be

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE REPLACE(REPLACE(DocID, 'DSRB', ''), 'DHA', '') != DocID

回复收藏 0 原文

冷血 2024-11-07 05:13:41

这：

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') AND (DocID LIKE '%DHA%')

只会匹配“DSRB DHA”之类的内容

您的意思是：

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') OR (DocID LIKE '%DHA%')

或者这个：

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%')
UNION
SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DHA%')

This:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') AND (DocID LIKE '%DHA%')

would only match something like "DSRB DHA"

You mean:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') OR (DocID LIKE '%DHA%')

Or this:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%')
UNION
SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DHA%')

回复收藏 0 原文

~没有更多了~