How to Use NVL in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
NVL(expr1, expr2) returns expr2 when expr1 is null. If expr1 is a number and expr2 is non-numeric text, Oracle raises ORA-01722.
SELECT employee_id, NVL(commission_pct, 0) AS commission_pct
FROM employees;NVL2 for a two-way branch
NVL2(expr1, expr2, expr3) returns expr2 when expr1 is not null and expr3 when it is, which replaces a small CASE for a simple not-null test.
SELECT customer_id,
NVL2(phone, 'has phone', 'no phone') AS phone_status
FROM customers;NVL determines its result type from the first argument, then implicitly converts the second to match. NVL(bonus, 0) is fine when bonus is a NUMBER, but NVL(some_number_column, 'N/A') raises ORA-01722: invalid number, because Oracle tries to convert 'N/A' to a number rather than converting the column to text. Cast explicitly instead: NVL(TO_CHAR(some_number_column), 'N/A').
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free