Blank vs NULL — Solution & ExplanationProblems / Blank vs NULL / EditorialApproach

Walk the three states with a CASE ladder, NULL first:

comments IS NULL → 'missing'. This must be the first branch — any comparison against NULL (like comments = '') evaluates to UNKNOWN, never TRUE, so a blank-check placed first would silently misroute NULLs.

NULLIF(TRIM(comments), '') IS NULL → 'blank'. TRIM reduces a spaces-only value to '', and NULLIF(x, '') converts that '' into NULL — so a single IS NULL test catches both empty strings and whitespace padding.

ELSE 'has_text' — anything left has at least one non-space character, even if it arrived with leading/trailing spaces.

Order by comment_id.

Key InsightNULL and empty string are not the same thing (unless you're on Oracle, which infamously conflates them). NULL means the value was never captured; '' and ' ' mean a value was captured but is content-free. Pipelines usually need to distinguish "never asked" from "answered with nothing".NULLIF(TRIM(col), '') is the workhorse idiom here: it canonicalizes every flavor of blank into NULL, after which the regular NULL machinery (IS NULL, COALESCE, COUNT(col)) treats blanks and NULLs uniformly. Flip it around — COALESCE(NULLIF(TRIM(col), ''), 'default') — and you get "replace blanks and NULLs with a default" in one line.Hints

NULL and '' are different values: NULL means "no data was ever recorded", while '' is a real (zero-length) string. WHERE comments = '' will never match the NULL rows.

Check the states in order: comments IS NULL first, then blank. If the blank check came first, comparing a NULL to '' would be UNKNOWN and the row would fall through to the wrong branch.

TRIM collapses a spaces-only string down to '', so blank means TRIM(comments) = ''. The idiom NULLIF(TRIM(comments), '') goes one step further: it maps every blank to NULL, giving you one IS NULL test for the blank branch.

In PySpark, chain F.when(F.col("comments").isNull(), ...).when(F.trim(...) == "", ...).otherwise(...).

Solve Blank vs NULL yourself →