SQL NULLIF Function: Clean Empty Strings and Prevent Division by Zero

By Sharon Ben-Moshe · October 5, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Deep dive into SQL NULLIF(a, b). Learn how to convert empty strings to NULL, safeguard mathematical formulas, and pair NULLIF with COALESCE.

Quick answer

The SQL NULLIF(expression1, expression2) function compares two expressions. If expression1 equals expression2, it returns NULL; otherwise, it returns expression1. It is the ANSI SQL shorthand for CASE WHEN expression1 = expression2 THEN NULL ELSE expression1 END, making it ideal for converting blank strings ('') to NULL and preventing division by zero.

FAQs

What does NULLIF return if the expressions do not match?

It returns the first argument unchanged. For example, `NULLIF('Active', '')` returns `'Active'`.

How does NULLIF differ from COALESCE?

COALESCE takes multiple arguments and returns the first non-NULL value. NULLIF takes exactly two arguments and converts matching values into NULL. They are complementary: NULLIF produces NULLs, and COALESCE replaces NULLs.

Can NULLIF convert blank strings to NULL for database normalization?

Yes! Running `NULLIF(TRIM(input_text), '')` converts empty or whitespace-only strings into SQL NULL, enforcing clean database hygiene.

Is NULLIF supported in all SQL dialects?

Yes, NULLIF is part of the core ANSI SQL standard and is supported identically across PostgreSQL, MySQL, SQL Server, SQLite, and BigQuery.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Cleaning empty strings in user data

Web forms frequently submit blank strings `''` for optional fields like phone numbers or middle names. Storing empty strings alongside NULL creates messy data. NULLIF normalizes this instantly:

Converting empty strings to NULL with NULLIF

-- Without NULLIF: verbose CASE statement
SELECT
  CASE
    WHEN TRIM(phone_number) = '' THEN NULL
    ELSE TRIM(phone_number)
  END AS clean_phone
FROM leads;

-- With NULLIF: clean and concise
SELECT
  NULLIF(TRIM(phone_number), '') AS clean_phone
FROM leads;

Pairing NULLIF with COALESCE

When consuming legacy systems that use sentinel values (like -1, 9999, or 'N/A') to represent missing data, chaining NULLIF into COALESCE enables clean data pipelines:

Handling legacy sentinel values with NULLIF and COALESCE

-- Replace sentinel '-1' with actual default rate 0.05
SELECT
  account_id,
  COALESCE(
    NULLIF(interest_rate, -1.0),
    0.05
  ) AS effective_interest_rate
FROM loan_applications;

Common mistakes to avoid

Passing mismatched data types: in PostgreSQL, `NULLIF(int_col, '')` throws a type error. Both arguments must have compatible data types.

Confusing argument order: `NULLIF(a, b)` returns `a` on mismatch, never `b`.

Assuming NULLIF checks for NULL: `NULLIF(col, NULL)` does not check IS NULL; use COALESCE or IS NULL instead.

SQL NULLIF Function: Clean Empty Strings and Prevent Division by Zero

Quick answer

FAQs

What does NULLIF return if the expressions do not match?

How does NULLIF differ from COALESCE?

Can NULLIF convert blank strings to NULL for database normalization?

Is NULLIF supported in all SQL dialects?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Cleaning empty strings in user data

Pairing NULLIF with COALESCE

Common mistakes to avoid

Official references

Quick answer

FAQs

What does NULLIF return if the expressions do not match?

How does NULLIF differ from COALESCE?

Can NULLIF convert blank strings to NULL for database normalization?

Is NULLIF supported in all SQL dialects?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Cleaning empty strings in user data

Pairing NULLIF with COALESCE

Common mistakes to avoid

Related SQL guides

Official references