title: SQL Query - SELECT all rows except where value in column begins with 402 or 403 or A40
body: <p>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.</p>
  
  <p>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.</p>
  
  <p><code>SELECT * from sqlsrvlink.DATABASE.dbo.V_PILOT_GENERAL WHERE NOT LEFT(pilot,3) IN ('402','403')</code></p>
  
  <p>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:</p>
  
  <blockquote>
    <p>Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value 'A406XX' to a column of data type int.</p>
  </blockquote>
  
  <p>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).</p>