Case When statement

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

Best Answers

  • user09644
    user09644 ⚪️
    edited February 25 Accepted Answer

    @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
    MarkSnodgrass Portland, Oregon 🟤
    Accepted Answer

    @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.

Answers

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!