title: Query to Concatenate Address
body: <p>I need to figure out how to concatenate mailing addresses in the following format:</p>
  
  <pre><code>address1 address 2 (sometimes not present) city state zip5 - zip4 (sometimes not present
  </code></pre>
  
  <p>I have tried to do this from an example I found online:</p>
  
  <pre><code>Stuff(  
      Coalesce(' ' + Buyer_Address1, '') + 
      Coalesce(' ' + Buyer_Address2, '') + 
      Coalesce(' ' + Buyer_City, '') + 
      Coalesce(' ' + Buyer_State, '') + 
      Coalesce(' ' + Buyer_ZipFive, '') +
      Coalesce('-' + Buyer_ZipFour, ''), 1, 1, '') AS [MailingAddress]
  </code></pre>
  
  <p>To these addresses:</p>
  
  <pre><code>Address 1               Address 2   City    State   Zip
  210 Independence Blvd               Tucson  AZ  85641
  1 Palace Lane           Suite A     Toad Kingdom    FL  37058
  123 Star Lane                       Star Kingdom    OH  54678
  </code></pre>
  
  <p>But the results are:</p>
  
  <pre><code>210 Independence Jr Blvd Tucson AZ 85641-
  1 Palace LaneSuite A Toad Kingdom FL 37058-
  123 Star Lane Star Kingdom OH 54678-
  </code></pre>
  
  <p>Any help in resolving these would be great.</p>