title: CAST failed when converting the varchar value to int
body: <p>Microsoft SQL Server 2008 R2, getting an unexpected 'Conversion failed' error.</p>
  
  <p>Not quite sure how to describe this problem, so here is a simple example. When I parse some column in the database like this:</p>
  
  <pre><code>SELECT CAST(RIGHT(CurLovim,LEN(CurLovim)-1) As INT) FROM
  (
   SELECT SUBSTRING(...) as CurLovim
   FROM [BackOfficeFut].[dbo].[our_orders]
   WHERE isin_id IN 
   (
    SELECT distinct isin_id 
    FROM [BackOfficeOpt].[dbo].[opt_sess_contents]
    WHERE base_isin_id = 335568
   )
  )as t1
  </code></pre>
  
  <p>I get an error: </p>
  
  <blockquote>
    <p>Conversion failed when converting the varchar value 'UR' to data type int.</p>
  </blockquote>
  
  <p>But if I change the condition on <code>isin_id</code> this way (it's equivalent):</p>
  
  <pre><code>SELECT CAST(RIGHT(CurLovim,LEN(CurLovim)-1) As INT) FROM
  (
   SELECT SUBSTRING(...) as CurLovim
   FROM [BackOfficeFut].[dbo].[our_orders]
   WHERE isin_id != 335568
  )as t1
  </code></pre>
  
  <p>the query works well.</p>
  
  <p>I find it very strange because if I remove <code>CAST</code>, i.e. make queries <code>SELECT RIGHT(CurLovim,LEN(CurLovim)-1) FROM ...</code> they return exactly the same result.</p>
  
  <p>What causes this error? Is this a known issue?</p>