Skip to content
Advertisement

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

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement