INSERT query with CASES that all share the same test statement

Question

I have an INSERT INTO query and I have CASE statements that determine many of the field values. In the following however all the test statements are the same, is there any way to consolidate this?

field1 = CASE WHEN IID <> '\' THEN IID ELSE '' END 
field2 = CASE WHEN IID <> '\' THEN Left(IID, (InStrRev(IID, '\') - 1)) 
         ELSE '' END
field3 =  CASE WHEN IID <> '\' THEN Right(IID, (Len(IID) - InStrRev(IID, '\')))

Answer 1

CASE is an expression (not a statement) that returns a single value. I can't think of any way to accomplish consolidation here, except to simplify the expression you're checking. For example, you could use a subquery or CTE:

;WITH x AS 
(
  SELECT x = CASE WHEN IID <> '\' THEN 1 ELSE 0 END, other columns 
  FROM dbo.table
)
INSERT INTO ...
SELECT column1 = CASE x WHEN 1 THEN IID ELSE '' END,
       column2 = CASE x WHEN 1 THEN LEFT ...
...etc
FROM x;