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:
So it would look like
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?
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
case expression stops at the first expression that evaluates to true.