Multiple WHEN in CASE only applies first two

I have a CASE with 4 WHENs. The first two applie fine, but the last two (or any others I add past that) do not get applied. All WHENs are referring to the same field.

Here’s what I’m trying to do. The field in question is formatted as:

[Indexname]_[Company]_[Product]_[StartDate$EndDate]

So it would look like

Retail_Walmart_Stores_Inc_Chair_03012012$05312015

The first Case I wrote was to eliminate the date

CASE WHEN FIELD1 LIKE '%$%' THEN REPLACE(FIELD1,RIGHT(FIELD1,17),'') ELSE FIELD1 END AS NewField

That works great. Then I want to remove the Index Name so I added a second WHEN. That worked also. But then, our company will have the full legal name of the company we’re doing business (so “Wal-Mart Stores, Inc” or “The Walt Disney Company”. For readability, I want those records to just say “WalMart” or Disney.”

My new code reads as

CASE                                                                                             
   WHEN FIELD1 LIKE '%$%'                                                                        
     THEN REPLACE(FIELD1,RIGHT(FIELD1,17),'')                                                      
   WHEN FIELD LIKE '%RETAIL%'                                                                 
     THEN REPLACE(FIELD1,'RETAIL','')                                                            
   WHEN FIELD LIKE '%STORES_INC%'                                                                 
     THEN REPLACE(FIELD1,'STORES_INC','')                                                        
   WHEN FIELD LIKE '%THE_WALT_DISNEY_COMPANY%'                                                                 
     THEN REPLACE(FIELD1,'THE_WALT_DISNEY_COMPANY','DISNEY')                                                        
   ELSE FIELD1                                                                                    
   END AS NewField1

I don’t understand why this stops working after the first two. At first I thought maybe it’s because they are all referring to the first column, but then the second WHEN wouldn’t have worked either.

Anyone know what’s going on?

Answer

If you want to do all the replacements rather than just the first one:

REPLACE(REPLACE(REPLACE(CASE WHEN FIELD1 LIKE '%$%' THEN REPLACE(FIELD1, RIGHT(FIELD1, 17), '') ELSE FIELD1
                        END, 'RETAIL', ''
                       ), STORES_INC', ''
               ), 'THE_WALT_DISNEY_COMPANY', 'DISNEY'
       )  AS NewField1

A case expression stops at the first expression that evaluates to true.

Advertisement

Answer