SQL Query - SELECT all rows except where value in column begins with 402 or 403 or A40

Question

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.

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.

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:

Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'A406XX' to a column of data type 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).

Answer 1

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.

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.

Answer 2

This might be OK. I think your 'NOT' wandered a bit far to the left...

SELECT *
    FROM sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL
    WHERE LEFT(pilot,3) NOT IN ('402','403', 'A40');

Answer 3

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 ...