Empty string equals NULL

pre-11.2 · sql

Lives on unchanged in Azure.

✓ certified by the mortician

In life

Oracle treats a character value of zero length as NULL. Inserting an empty string stores NULL, comparisons with an empty literal behave like comparisons with NULL, and concatenation is the one operator that tolerates null operands. Oracle's SQL reference explicitly says this may not remain true in future releases and advises applications not to depend on it.

Migrations care because SQL Server and PostgreSQL distinguish an empty string from NULL. NOT NULL constraints that Oracle accepted silently start rejecting empty values or, conversely, allow empty strings that Oracle would have stored as NULL; equality tests, LENGTH results, and COALESCE and NVL patterns all shift. Every empty-string literal and every IS NULL test on character columns in the source code is a review item.

The behavior is long-standing and unchanged as of 26ai; it is documented, not deprecated, but flagged by Oracle as something not to rely on.

Cause of departure

Not deprecated by Oracle. Listed here because Azure offers no clean equivalent.

Obituary

Empty string equals NULL, born before 11.2, made every zero-length VARCHAR2 quietly become NULL. Applications accordingly never met an empty string, a modest disappearance with substantial consequences.

Its passage to Azure SQL Database and Azure Database for PostgreSQL Flexible Server was blocked because both preserve ‘’ as a non-NULL, zero-length value. Thus IS NULL predicates miss it, while NOT NULL constraints may unexpectedly admit it.

It is survived by Azure Database for PostgreSQL Flexible Server, only by workaround; Azure SQL Database, only by workaround; and Oracle Database@Azure, where it lives on unchanged. Ora2Pg can rewrite tests, orafce can replace empty strings on write, and explicit handling remains necessary elsewhere.

Survived by

Oracle Database@Azure exact effort S

Applies to Oracle 11.2, 12.1, 12.2, 18c, 19c, 21c, 23ai, 26ai

Oracle Database@Azure runs Oracle Database itself (Exadata Database Service, Base Database Service or Autonomous Database) inside Azure datacenters, so SQL semantics are unchanged and a zero-length VARCHAR2 is still stored and compared as NULL. The behaviour moves with the database; only a later move to a non-Oracle engine reintroduces the difference.

Keep existing NULL-handling code; no rewrite is needed for this behaviour.
Record the dependency in the migration inventory so a future hop to Azure SQL or PostgreSQL does not miss it.
Note that Oracle's SQL reference warns applications not to rely on this behaviour in future releases.

Azure Database for PostgreSQL Flexible Server workaround effort M

Applies to Oracle 11.2, 12.1, 12.2, 18c, 19c, 21c, 23ai, 26ai

PostgreSQL distinguishes '' from NULL exactly as SQL Server does. Ora2Pg offers NULL_EQUAL_EMPTY, which rewrites IS NULL and IS NOT NULL tests into coalesce()-based checks so converted code keeps Oracle's behaviour, and its documentation recommends instead converting empty strings to NULL when the application can be changed. orafce adds a trigger function that replaces empty strings with NULL on write.

Choose between NULL_EQUAL_EMPTY rewriting and normalizing data at write time.
If normalizing, add orafce's replace_empty_strings trigger or an application-side rule.
Re-express NVL(col, 'x') logic that assumed '' was NULL with coalesce(nullif(col, ''), 'x').
Test NOT NULL columns that Oracle allowed to be "empty" by rejecting the insert.

Complications

major PostgreSQL treats '' as a non-NULL value; Ora2Pg's NULL_EQUAL_EMPTY directive exists to rewrite IS NULL tests into coalesce()-based checks precisely because PostgreSQL differentiates empty strings from NULL, and its documentation recommends converting empty strings to NULL when the application can be changed.

· community Ora2Pg documentation (configuration directives)

Azure SQL Database workaround effort M

Applies to Oracle 11.2, 12.1, 12.2, 18c, 19c, 21c, 23ai, 26ai

Oracle stores a zero-length VARCHAR2 as NULL, so applications never see an empty string. SQL Server keeps '' as a real value distinct from NULL, and Microsoft's documentation states that a null value differs from an empty or zero value. After migration the same INSERT of '' produces a non-null row, IS NULL predicates stop matching it, and NOT NULL constraints that used to reject '' now accept it, so data and code need explicit handling.

Decide a policy: normalize '' to NULL on write (application or trigger) or treat '' and NULL as equivalent in queries with NULLIF.
Add CHECK constraints or triggers that reject '' where Oracle's implicit NULL enforced NOT NULL.
Enable SSMA's ISNULL-in-concatenation option so NULL || 'x' keeps Oracle's result.
Search code for = '' and <> '' comparisons, which were always false or unknown in Oracle.

Complications

major In SQL Server and Azure SQL an empty string '' is a stored zero-length value that is distinct from NULL; Microsoft's NULL documentation states that a null value is different from an empty or zero value, so Oracle code that relied on '' being NULL changes behaviour.

· microsoft NULL and UNKNOWN (Transact-SQL)

Notices of correction

oracle Nulls (SQL Language Reference 19c) · SQL Language Reference 19c · read 2026-09-26
microsoft Overview: Oracle Database@Azure · page updated 2026-09-15 · read 2026-09-26
community Ora2Pg documentation (configuration directives) · Ora2Pg 25.0 (changelog 2025-04-20); text read from doc/Ora2Pg.pod in the GitHub repository · read 2026-09-26
community orafce README (Oracle compatibility functions and packages for PostgreSQL) · orafce 4.x (Azure PG Flex ships 4.16 for PostgreSQL 14-18) · read 2026-09-26
microsoft NULL and UNKNOWN (Transact-SQL) · page updated 2026-09-21 · read 2026-09-26
microsoft SSMA for Oracle: Project Settings (Conversion) · SSMA for Oracle, page updated 2026-08-27 · read 2026-09-26

← All notices