title: case and concatenation, sql
body: <p>Im using a SQL server database and im having a hard time with my case statement.  What I'm trying to do is insert a concatenation of attributes into an id field (state_issue_teacher_id) when it equals '' (empty field).  The problem is that I have some empty id fields that I need to fill with a unique id that is created by a concatenation of attributes through the case statement.</p>
  
  <p><img src="https://i.stack.imgur.com/GXHH0.gif" alt="enter image description here"></p>
  
  <p>The empty rows above I need to fill with a concatenation of last_name+first_name+gender+race_ethnicity_code+high_degree_code+position_code+assignment_code.</p>
  
  <p>Here is what I have so far:</p>
  
  <pre><code>SELECT    
          state_issue_teacher_id,
          region_code
          + county_code
          + district_code AS district_code ,
          last_name ,
          first_name ,
          assigned_fte = CASE assign_fte
                    WHEN '' THEN 0
                    ELSE CAST(assign_fte AS NUMERIC(18,
                    2))
                    END ,
          CASE WHEN state_issue_teacher_id = '' THEN 
          RTRIM([last_name]) + '_' + RTRIM([first_name]) + '_' + RTRIM([gender])
          + '_' + RTRIM([race_ethnicity_code]) + '_' + high_degree_code + '_'
          + position_code  + '_' + assignment_code
          ELSE state_issue_teacher_id
          END,
          year_time
  
  FROM      dbo.example
  </code></pre>