Case Statement Issues

Database

Case Statement Issues

I have the following Code:

 

Select
CASE
WHEN CSQ_Name like '%_CSQ' THEN OREPLACE(CSQ_Name, '_CSQ','')
WHEN CSQ_Name like '%CSQ' THEN OREPLACE(CSQ_Name, 'CSQ','')
ELSE CSQ_Name
END as Program_Name
FROM ......

 

So in the data there exists CSQ_Names with the following values

 

Name_CSQ

NameCSQ

 

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?

 

Thanks

Brian Kucharski

3 REPLIES
Teradata Employee

Re: Case Statement Issues

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

 

Dave

Senior Supporter

Re: Case Statement Issues

check the "ESCAPE Feature of LIKE"

 

so replaceing

WHEN CSQ_Name like '%_CSQ'

with

WHEN CSQ_Name like '%Z_CSQ' ESCAPE 'Z'

 

should give you the expected result

Re: Case Statement Issues

Thanks Ulrich, I am new to Teradata so I was unaware of the underscore issue.  Thanks for the information, worked like a charm.