title: Use Collate in CONCAT
body: <p>I was trying to concatonate 2 columns with a whitespace in between and got a collation error:</p>
  
  <pre><code>SELECT DISTINCT
      p.PERSON_ID,
      p.ID_NUMBER,
      CONCAT(p.FULLNAMES, CONCAT(' ', p.SURNAME)) AS NAME,
      o.ORG_NAME,
      w.WARD_DESCRIPTION AS WARD,
      ess.DESCRIPTION AS SECTOR
  </code></pre>
  
  <blockquote>
    <p>Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the concat operation</p>
  </blockquote>
  
  <p>The collation of both the offending columns in my database is: <code>Latin1_General_CI_AS</code></p>
  
  <p>So then I was trying to collate the whitespace to this collation, but I have no idea how to do this. My attempt:</p>
  
  <pre><code>CONCAT(p.FULLNAMES, (CONCAT((COLLATE Latin1_General_CI_AS = ' '), p.SURNAME))) AS NAME,
  </code></pre>
  
  <p>or something?</p>