title: Removing carriage returns using SQL
body: <p>I have the following SQL which concatenates my address fields into <code>ADDRESSLINE1</code>:</p>
  
  <pre><code>SELECT Isnull(ADDRESSLINE1 + Char(13)+Char(10), '')
         + Isnull(ADDRESSLINE2 + Char(13)+Char(10), '')
         + Isnull(ADDRESSLINE3, '') AS ADDRESSLINE1
  </code></pre>
  
  <p>It returns:</p>
  
  <pre><code>ADDRESSLINE1       
  ------------------ -----------------------------------
  10 TEST ROAD
  
  
  
  (1 row(s) affected)
  </code></pre>
  
  <p>How do I amend my code so that if <code>ADDRESSLINE2</code> AND <code>ADDRESSLINE3</code> are blank '' then remove the unnecessary carriage returns. </p>
  
  <p>I would expect:</p>
  
  <pre><code>ADDRESSLINE1       
  ------------------ -----------------------------------
  10 TEST ROAD
  
  (1 row(s) affected)
  </code></pre>