Exception in case statement

Question

How would you throw an exception in a case statement? I believe signal is the correct statement. The case statement is when the word 'corp' is found in the name then have a 1 in the column but I want an exception if the word 'dollar' is in the name

Name                     Is_comp
Dollar Tree Corp       
First Hand Corp           1

Select 
when name like 'corp' 
then 1 
end as is_comp

Answer 1

I think, based on what you are looking for, you just need a fancier CASE statement:

SELECT
    CASE 
        WHEN 
            name LIKE ANY ('%corp%','%llc%','%inc%') /*list of names with wildcards*/
            AND name not like '%Finch%' /*don't match on finch%/ 
            AND name not like ANY ('%Finch%','%Brian%') /*or optionally don't match on any of these name substrings*/
            THEN 1 
        END 'is_comp'
FROM <table>