Shape / columns · All operations

Turn values into nulls

A variant of Fill in missing values

Mark a given value, or the selected cells, as missing.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Turn a given value, or chosen cells, into nulls

SQL

CREATE TEMP TABLE raw AS SELECT * FROM (VALUES
  (1, 10, 'ok'), (2, -999, 'n/a'), (3, 30, 'N/A'), (4, -999, 'fine'), (5, 50, 'n/a')
) t(id, qty, note);
CREATE TEMP TABLE result AS
SELECT id,
       nullif(qty, -999) AS qty,
       nullif(note, 'n/a') AS note,
       CASE WHEN id IN (3, 4) THEN NULL ELSE note END AS note_cells
FROM raw;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(qty ORDER BY id) FROM result) = [10, NULL, 30, NULL, 50] AND (SELECT list(note ORDER BY id) FROM result) = ['ok', NULL, 'N/A', 'fine', NULL] AND (SELECT list(note_cells ORDER BY id) FROM result) = ['ok', 'n/a', NULL, NULL, 'n/a']) THEN 'as expected' ELSE error('the sentinel -999 and the text n/a were not turned into nulls, or the chosen cells in rows 3 and 4 were not') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the sentinel -999 and the text n/a were not turned into nulls, or the chosen cells in rows 3 and 4 were not”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)

Null Ifread 2026-09-27
Set a field to null where it equals a given value

Ibis (1)

NullIfread 2026-09-27
Turn a value into null when it equals a given value

OpenRefine (2)

To empty stringread 2026-09-27
Set the cells in the current selection to a zero-length string
To nullread 2026-09-27
Set the cells in the current selection to null

Pentaho Data Integration (1)

Null if...read 2026-09-27
Set a field to null where it equals a constant value

RapidMiner Studio (2)

Declare Missing Valueread 2026-09-27
Mark a given value, or values matching an expression, as missing
Declare Missing Value (In Database)read 2026-09-27
Mark a given numeric or nominal value as missing, executed in the database

VisiData (2)

delete-cellread 2026-09-23
Set current cell to null
delete-cellsread 2026-09-23
Set current column to null for selected rows

Background

In SQL, NULLIF takes two parameters and returns null when the first is equal to the second, and otherwise returns the first.^[1] In SQL, NULLIF is an abbreviation for CASE WHEN value1 = value2 THEN NULL ELSE value1 END.^[1]

When to use it

In SQL, every aggregate function except COUNT(*) leaves nulls out, so an average over 150, 200, 250 and 0 is 150, where with a null in place of the 0 it is 200.^[1] Turn a code such as -999 into a null before any summary reads the column. (Meridian's view)

DuckDB compares strings on their binary contents by default.^[2] DuckDB's NOCASE collation compares characters as equal regardless of their casing.^[2] List every spelling the source uses for missing, such as 'n/a' and 'N/A', or compare in one case, before calling NULLIF. (Meridian's view)

In his 1990 book The Relational Model for Database Management, Version 2, Codd argued that the single null mandated by the SQL standard was inadequate and should be replaced by two markers saying why data is missing.^[1] Codd's two markers stood for 'Missing But Applicable' and 'Missing But Inapplicable', and the idea of several nulls has not gained widespread acceptance among database practitioners.^[1] When a source uses different codes for reasons such as 'not asked' and 'refused', keep the code in a column of its own before turning both into null. (Meridian's view)

The method

DuckDB's nullif(a, b) returns NULL if a = b and otherwise returns a.^[3] In PostgreSQL, NULLIF compares its arguments exactly as value1 = value2 would, so a suitable = operator must be available.^[4] In PostgreSQL, NULLIF(1, 2.2) yields numeric, because there is no integer = numeric operator and the first argument is promoted to match.^[4] Apache Hop's Null If transform sets a field to null when its value equals a value given in the transform.^[5]

DuckDB's CSV reader takes a nullstr option, a single string or a list of strings that represent a NULL value, and its default is the empty string.^[6] By default pandas.read_csv reads an empty string, 'NaN', 'N/A', 'NULL' and other common representations of missing data as NaN, and its na_values parameter adds further strings.^[7]

Sources

[1]Null (SQL) - Wikipediasecondary source
[2]Collations – DuckDBdocumentation
[3]Utility Functions – DuckDBdocumentation
[4]PostgreSQL: Documentation: 18: 9.18. Conditional Expressionsdocumentation
[5]Null If | Apache Hopdocumentation
[6]CSV Import – DuckDBdocumentation
[7]pandas.read_csv — pandas 3.1.0 documentationdocumentation