Simple CASE vs searched CASE - LIKE predicate

Question

Accepted Answer

Simple CASE (CASE expression WHEN value ...) tests for equality and nothing else. Writing CASE name WHEN 'Pro%' THEN ... compares name to the literal string 'Pro%', not a pattern. To use LIKE, BETWEEN, IS NULL, or any other predicate, you need the searched CASE form where the full predicate goes in the WHEN clause. The searched form is strictly more powerful - simple CASE is just a convenience shorthand for pure equality checks.