title: CAST/CONVERT empty string to INT in SQL Server
body: <p>I came across a bug where I was using <code>CAST(Col1 AS INT) + CAST(Col2 AS INT)</code> where both Col1 and Col2 are <code>VARCHAR</code> and I was getting valid results out when Col1 or Col2 was blank and I didn't expect this. I checked and CAST (and CONVERT) both have this default behavior of replacing blank with 0:</p>
  
  <pre><code>SELECT CAST('' AS INT)
  SELECT CONVERT(INT, '')
  </code></pre>
  
  <p>I checked the <a href="https://msdn.microsoft.com/en-GB/library/ms187928.aspx" rel="noreferrer">info page</a> and I can't see any reference to explain why this is the behavior (or change it through a server setting). I can of course work around this but I wanted to ask why this is the behavior as I do not think it is intuitive. </p>
  
  <p>I'd actually rather this <code>CAST</code> failed or gave <code>NULL</code>, is there a server setting somewhere which effects this?</p>