title: How to control casting of null int field to varchar in sql server?
body: <p>First of all I would like to know how does <code>CAST</code> work with <code>NULL</code> fields and how does it behave when the value is <code>NULL</code>?</p>
  
  <p>For example in the expression:</p>
  
  <pre><code>(CAST(INT_FIELD as nvarchar(100))
  </code></pre>
  
  <p>what happens if the value <code>INT_FIELD</code> is <code>NULL</code>?</p>
  
  <p>The reason is that when I'm trying to do the following:</p>
  
  <pre><code>SELECT (CAST(INT_FIELD as nvarchar(100)) + ' ' + SOME_OTHER_FIELD FROM SOME_TABLE;
  </code></pre>
  
  <p>I'm getting <code>NULL</code> even though the <code>SOME_OTHER_FIELD</code> is not null. I'm guessing it has some kind of logic that <code>NULL + something = NULL</code> but I'm not sure.</p>
  
  <p>How can I control this behavior?</p>