Reference · SQL (DuckDB)
Turn blank text into missing in SQL (DuckDB)
Convert empty text into a missing value so blanks and missing values are handled by one rule.
When to use it
After trimming, when the rules say an empty result means nothing was recorded.
How
NULLIF(x, '') returns NULL when x is the empty string.
NULLIF(trim(customer_key, ' ' || chr(9) || chr(13) || chr(10)), '')
- Combine with trim in one expression.
Pitfalls
- Running it before trimming.
- Treating zero as blank.
Lesson: Missing is not empty (and neither is zero)
Practise it: Join orders to customers without matching blanks