If statement within switch

Question

I know I'm just thinking of the logic all wrong here but how do I achieve the following:

update @table   
             set   column1 = case 
               when column1 <> '' 
                   then rtrim(column1) + ', ' + rtrim(column2)--if statement here 
               else     rtrim(column2)              
               end
            from @othertable

I basically want to check if rtrim(column 2) = 'value' then replace it with something else. I understand this is within a switch statement, so how would this be acheived?

Answer 1

  update @table   
         set   column1 = case 
           when column1 <> '' 
               then rtrim(column1) + ', ' + 
              case 
              when column2 = 'value' 
                 then rtrim(column2) 
              else ... 
              end
           else  rtrim(column2)              
           end
        from @othertable

Answer 2

may be you don't need any switch and if clause:

update @table set column1 = rtrim(isnull(nullif(column1,'') + ', ', '')) + rtrim(column2)

Answer 3

Use a REPLACE within your CASE statement

eg: REPLACE(rtrim(column2),'value','newValue');

Refer MSDN for more details on REPLACE