Case When statement

user082291

Is it possible to use contains instead of = in the Case When statement

CASE when 'Amount'=1 then 10 else 0 End

For example: Case when 'Reason' contains 'Retal' then 'Retaliation' Else 'Other' End

we have many different variations of Retaliation statements and don't want to spell each one out in a case when statement

Fear of Retaliation, I feel retaliated, Fear retaliated against, etc.

want to combine all the variations into one bucket of Retaliation.

Thank you, Jill Bowers

user09644

@user082291 You'll want to use wildcard character to achieve this. I like to wrap the upper() function to remove casing variability.

CASE WHEN UPPER('REASON') LIKE ('RETAL%') then 'Retaliation' ELSE 'Other' END

MarkSnodgrass

@user09644 is spot on. Additionally, adding the % before the word will allow you to look for that word anywhere in the phrase. If you only have the % at the end, then you are requiring that your phrase starts with the word retail. Using '%RETAIL%' will allow it to search for that word anywhere in the phrase.

user09644

@user082291 You'll want to use wildcard character to achieve this. I like to wrap the upper() function to remove casing variability.

CASE WHEN UPPER('REASON') LIKE ('RETAL%') then 'Retaliation' ELSE 'Other' END

MarkSnodgrass

@user09644 is spot on. Additionally, adding the % before the word will allow you to look for that word anywhere in the phrase. If you only have the % at the end, then you are requiring that your phrase starts with the word retail. Using '%RETAIL%' will allow it to search for that word anywhere in the phrase.

user082291

Thank you for the prompt answer.  This is what I was looking for.