Search code examples
sqlcasewhere-clausesql-null

SQL CASE that matches value or uses LIKE '%'

I'm trying to do a wildcard retrieve if a defined variable is blank. But I can't figure out the correct syntax.

SELECT ...
FROM ...
WHERE customers = CASE
    WHEN ISNULL(@customerID, '') = '' THEN LIKE '%'
    ELSE @customerID
END
Solution

  • It is as simple as this:

    WHERE customers = NULLIF(@customerID, '') OR NULLIF(@customerID, '') IS NULL

    The expression NULLIF(x, '') will convert '' to null. Then we take advantage of how null comparison works:

    • If @customerID is not null then first condition will be true only for exact match
    • If @customerID is null then first condition will be false (nothing equals null) but second condition will be true for all rows

    DB<>Fiddle