I have the following Code:
WHEN CSQ_Name like '%_CSQ' THEN OREPLACE(CSQ_Name, '_CSQ','')
WHEN CSQ_Name like '%CSQ' THEN OREPLACE(CSQ_Name, 'CSQ','')
END as Program_Name
So in the data there exists CSQ_Names with the following values
I would assume the above would scrub both occurances, but written as is the _CSQ values are scrubbed while the NameCSQ values are not. If I comment out the first WHEN clause then all values of CSQ (The second WHEN) works. What is happening with the two WHEN clauses with the like and replace as is it is almost like it is only fireing the first when and immediately dropping to the ELSE?
The underscore is a single character wildcard like the % is a multi character wildcard.
So, the first case condition is always met first and applied. You could try reversing them
check the "ESCAPE Feature of LIKE"
WHEN CSQ_Name like '%_CSQ'
WHEN CSQ_Name like '%Z_CSQ' ESCAPE 'Z'
should give you the expected result