SQL 查询 - 选择除列中的值以 402 或 403 或 A40 开头的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3235565/
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 Query - SELECT all rows except where value in column begins with 402 or 403 or A40
提问by johnnyjosefsson
I have a (MS) SQL VIEW with no control over and it also queries several other SQL VIEWs that I also have no control over but must use.
我有一个无法控制的 (MS) SQL VIEW，它还查询其他几个我也无法控制但必须使用的 SQL VIEW。
Get all rows based on "pilot" value except where it starts with 402 or 403, or A40. This is the current query that works fine as long as all "pilot" entries are possible to interpret as INT.
根据“pilot”值获取所有行，但以 402 或 403 或 A40 开头的行除外。只要所有“试验”条目都可以解释为 INT，这是当前可以正常工作的查询。
SELECT * from sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL WHERE NOT LEFT(pilot,3) IN ('402','403')
SELECT * from sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL WHERE NOT LEFT(pilot,3) IN ('402','403')
The pilot should always be an INT but the SQL design leaves much to desire and is implemented as a VARCHAR. Therefore it's possible, for 3:rd party application with no input format checks, to 'configure' the "pilot" column to include none numerical values and in that case my SELECT statement fails with error message:
Pilot 应该始终是 INT，但 SQL 设计有很多不足之处，并作为 VARCHAR 实现。因此，对于没有输入格式检查的 3:rd 方应用程序，可以“配置”“试验”列以不包含数值，在这种情况下，我的 SELECT 语句失败并显示错误消息：
Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'A406XX' to a column of data type int.
消息 245，级别 16，状态 1，第 1 行将 varchar 值“A406XX”转换为数据类型为 int 的列时出现语法错误。
How can I expand the SELECT to also exclude A40, but mainly workaround the 'converting error of VARCHAR to INT' by excluding or bluntly ignore them (none numerical values).
如何扩展 SELECT 以排除 A40，但主要是通过排除或直接忽略它们（无数值）来解决“将 VARCHAR 转换为 INT 的错误”。
采纳答案by Martin Smith
SELECT * from sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL
WHERE pilot NOT LIKE '402%' AND pilot NOT LIKE '403%' AND pilot NOT LIKE 'A40%'
Should make better use of any index on pilot as well
也应该更好地利用飞行员的任何索引
Edit
编辑
Following your comment I think I misunderstood the problem originally. The issue is not with the SELECT statement itself but more when invalid data is in the base table any attempt to access that column in the offending row causes an error as below.
根据您的评论，我想我最初误解了这个问题。问题不在于 SELECT 语句本身，而在于基表中存在无效数据时，任何尝试访问违规行中的该列都会导致错误，如下所示。
CREATE VIEW [dbo].[testing]
AS
SELECT 1 AS [Number]
,'X' AS Letter
union all
SELECT 'bob' AS [Number]
,'Y' AS Letter
union all
SELECT 3 AS [Number]
,'Z' AS Letter
GO
SELECT * /*Works*/
FROM [dbo].[testing]
WHERE Letter IN ('X', 'Z')
SELECT CAST([Number] AS varchar(10)) /*Error Occurs*/
FROM [dbo].[testing]
I'm not aware of any way of avoiding this without changing the View definition (which you say you can't do) or changing the base data, or rewriting the query to not use the view. You won't be able to filter on that column to remove dodgy records as the attempt to use it in a filter will just cause the error to occur.
我不知道有什么方法可以在不更改视图定义（您说您不能这样做）或更改基本数据或重写查询以不使用视图的情况下避免这种情况。您将无法对该列进行过滤以删除可疑记录，因为尝试在过滤器中使用它只会导致发生错误。
回答by Brian Hooper
This might be OK. I think your 'NOT' wandered a bit far to the left...
这可能没问题。我认为你的“NOT”向左徘徊了一点......
SELECT *
FROM sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL
WHERE LEFT(pilot,3) NOT IN ('402','403', 'A40');
回答by Patkos Csaba
What about something like this? This should work for string/text fields.
SELECT * from sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL WHERE pilot not like(402%) or ...
这样的事情怎么办？这应该适用于字符串/文本字段。
SELECT * from sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL WHERE pilot not like(402%) or ...
