How can I use a case statement that includes a wildcard

Question

I have tried to include a wildcard in the below Case statement with no good result.

CASE 
    WHEN store IS NULL THEN
        (
            CASE
                WHEN location IN (‘A%’, ‘B%’’ ‘C%’) THEN ‘staff’ 
                ELSE store_code
        ) 
    ELSE store
    END AS sister_store

Answer 1

If the part preceding the wildcard is only one letter as in your example, then

CASE WHEN location like '[ABC]%' then

The particular syntax you are looking for is actually LIKE ANY (supported at least by Teradata but not by SqlServer afaik.

If the check is more complex you should split it into OR parts, like:

CASE WHEN location like 'A%' or location like 'B%' or location like 'C%' then

You can also use CHARINDEX/PATINDEX functions for more elaborate conditions.