NULL, empty, and spaces: three different inputs

A field that looks blank can represent several different inputs. Before cleaning a dataset, make the distinction visible. This small, synthetic demonstration contains eight text entries: two NULL values, two empty strings, two strings containing exactly three spaces, plus alpha and beta. These are fictional test values, not customer records.

What the first query counts

count(*) returns the number of rows. count(value) returns the number of non-NULL values in the selected column. Subtracting the second count from the first gives the number of NULL entries in this fixture. The observed output was eight total rows, six non-NULL values, and two NULL values. Empty strings and three-space strings are non-NULL values.

Exact parameterized queries

For each query, bind these eight values in this exact order: null, "", " ", "alpha", null, "", "beta", " ". The client must perform parameter binding; the placeholders are not literal values to paste into the SQL.

Query 1

WITH fixture(value) AS (
  VALUES ($1::text), ($2::text), ($3::text), ($4::text),
         ($5::text), ($6::text), ($7::text), ($8::text)
)
SELECT count(*) AS total_rows,
       count(value) AS non_null_values,
       count(*) - count(value) AS null_values
FROM fixture

Query 2

WITH fixture(value) AS (
  VALUES ($1::text), ($2::text), ($3::text), ($4::text),
         ($5::text), ($6::text), ($7::text), ($8::text)
)
SELECT CASE WHEN value IS NULL THEN 'NULL'
            WHEN value = '' THEN 'EMPTY'
            WHEN value = '   ' THEN 'THREE_SPACES'
            ELSE 'TEXT' END AS kind,
       value,
       length(value) AS character_count,
       count(*) AS rows_in_group
FROM fixture
GROUP BY value
ORDER BY kind, value

Quotation marks in the value column show string boundaries; they are not part of the stored strings.

What the comparison shows

The second query labels NULL, the empty string, and exactly three spaces separately. The original value remains in the result alongside its character count and the size of its group. The labels are explanatory output; they do not overwrite the fixture.

Five groups appeared. The empty-string group contained two rows and had a character count of zero. The NULL group also contained two rows, but its character-count result was NULL rather than zero. The three-space group contained two rows and had a character count of three. Finally, alpha and beta appeared in separate one-row groups, with character counts of five and four. Adding the group sizes gives eight, matching the first query's total.

This comparison is useful because an unlabelled display can make empty and whitespace-only text hard to distinguish. Inspecting the value together with an explicit category and character count exposes the difference demonstrated here. It also keeps an apparently empty field from being silently treated as missing information.

Keep diagnosis separate from cleanup

Do not turn this observation into an automatic cleanup rule. This exercise did not trim strings, replace empty strings with NULL, infer missing answers, or delete duplicate rows. Whether those actions would be appropriate depends on the data's meaning and the agreed requirements. Keep that decision separate from the diagnostic query.

Execution and limits

Both successful queries were executed in the company's native read-only SQL interface using the eight bound values above. The first submission was rejected because it ended with a terminal semicolon; removing only that terminal semicolon was the evidence-specific repair, and the corrected run succeeded. The second planned query then succeeded. This brief history does not claim portability.

The parameterized SQL is not a claim that every database client accepts this placeholder syntax unchanged. No database version, client, product, production compatibility, normalization, or cross-database claim is made. The demonstration does not establish customer work, customer proof, or a generalized normalization service.