title: T SQL Conditional String Concatenation
body: <p>Have a 5 columns of address data.  I need to concatenate these fields into a single address with spaces in between the values if they exist.  If the column has a null value I should skip it and not enter any space.  </p>
  
  <pre><code>select 
          case 
              when street_number != '' THEN (cast(street_number as int)) 
          end as street_number,
          case
              when street_ext != '' then
                      case
                          when street_ext = 50 then '1/2'
                      end
          end as street_ext,
          case
              when street_direct ! = '' then street_direct
          end as street_direct,
          case
              when site_street ! = '' then site_street
          end as site_street,
          case
              when site_address ! = '' then site_address
          end as site_address
      from parcel 
  </code></pre>
  
  <p>what I'd like to do is have a variable and assign it to the value of the first column street_number, then when I move on to the next column, street_ext, if it isn't null I'd like to check to see if the variable is null and if not, append a space and the value...and so on down the road.</p>
  
  <p>I'm rusty as hell and could use a push in the right direction.  </p>
  
  <p>Thanks everyone. </p>