title: Return Character from Numeric Field using Cast & Coalesce
body: <p>Using SQL Server 2008 R2</p>
  
  <p>Relatively basic SQL user, apologies if this is a simple question but need a little help to tidy up the output of a fairly complex script I have been given.</p>
  
  <p>I have a number of columns being returned for which where there is a NULL, I want to replace all NULL's with a standard set of characters, currently "---". Using <code>ISNULL</code> works for most columns. However, for some columns we are looking at 2 tables to find a value so have after doing some research on here, I have modified a line I am having trouble with as follows:</p>
  
  <p><strong>Previous</strong></p>
  
  <pre><code>isnull (ff.ff_sales,aa.ff_sales) as 'Total Revenue' 
  </code></pre>
  
  <p><strong>Latest</strong></p>
  
  <pre><code>cast(coalesce(ff.ff_sales,aa.ff_sales,'') as FLOAT) as 'Total Revenue'
  </code></pre>
  
  <p>The initial line returned 'NULL' if both ff.ff_sales &amp; aa.ff_sales were empty, now with the latest line using cast &amp; coalesce I get '0'. However, I am trying to achieve a situation where I get '---' as per all other fields where a NULL exists. I don't want it to return '0' for a Sales field as this is misleading. I have tried using <code>VARCHAR</code> instead of <code>FLOAT</code> but am unsure if this is the right thing to do at this stage?</p>
  
  <p>1st column is using <code>ISNULL</code>, 2nd column current output with cast &amp; coalesce, 3rd column is what I want to get to:</p>
  
  <pre><code>Total Revenue   Total Revenue     Total Revenue
  67755           67755             67755
  6.123           6.123             6.123
  494.75          494.75            494.75
  0               0                 0
  1139909         1139909           1139909
  12346.45        12346.45          12346.45
  129.866         129.866           129.866
  NULL            0                 ---
  NULL            0                 ---
  554             554               554
  </code></pre>
  
  <p>Thanks for your help!</p>