Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 38 ยท Functions and Expressions

Conversion and NULL Functions

Convert data types with TO_CHAR, TO_DATE and TO_NUMBER format models, handle NULL with NVL, NVL2, COALESCE and NULLIF, and branch with DECODE and CASE.

A database column has one type, but a report needs text, a form sends strings, and a calculation needs numbers. Oracle converts between types in two ways: implicitly, when it quietly guesses, and explicitly, when you call TO_CHAR, TO_DATE or TO_NUMBER. Explicit conversion is the habit to build, because implicit conversion depends on session settings and can fail on another machine.

This lesson also covers the functions that decide what to do with NULL (NVL, COALESCE, NULLIF) and the two ways Oracle chooses between values: CASE and the older DECODE.

TO_CHAR for numbers and dates

TO_CHAR(value, 'format model') turns a number or date into text. The format model is a pattern of codes.

Date codes:

Code Meaning Example for 2024-05-07 14:09:03
YYYY, YY Year 2024, 24
MM, MON, MONTH Month number or name 05, MAY, MAY
DD, DAY, DY Day of month, weekday 07, TUESDAY , TUE
HH24, HH12, AM Hour 14, 02, PM
MI, SS Minutes, seconds 09, 03
Q, IW, DDD Quarter, ISO week, day of year 2, 19, 128
FM Fill mode: remove padding and leading zeros
SQL
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;     -- 2024-05-07 14:09:03
SELECT TO_CHAR(DATE '2024-05-07', 'FMDay, Month DD, YYYY') FROM dual;  -- Tuesday, May 7, 2024

MONTH and DAY are padded to a fixed width; FM at the start removes that padding and the leading zeros, which is what you usually want for readable text.

Number codes:

Code Meaning
9 A digit, blank if a leading zero
0 A digit, zero shown if empty
, and . Group and decimal separators
$, L Dollar sign, local currency symbol
FM Remove leading spaces
SQL
TO_CHAR(1234567.891, 'FM9,999,999.00')   -- 1,234,567.89
TO_CHAR(7, '000')                        -- 007
TO_CHAR(0.5, 'FM0.00')                   -- 0.50
TO_CHAR(1234.5, 'FM$99,999.00')          -- $1,234.50

If the number does not fit the model, Oracle returns a string of hash signs (#####). Stand-in with SQLite's strftime and printf:

SQL Formatting dates and numbers (stand-in for TO_CHAR) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

TO_DATE and TO_NUMBER

The opposite direction needs a format model too, so Oracle knows how to read the text:

SQL
TO_DATE('07/05/2024', 'DD/MM/YYYY')              -- 7 May 2024
TO_DATE('2024-05-07 14:09', 'YYYY-MM-DD HH24:MI')
TO_NUMBER('1,234.50', '9,999.99')               -- 1234.5
TO_NUMBER('42')                                 -- 42

For literals in scripts, prefer ANSI literals, which have no format ambiguity: DATE '2024-05-07' and TIMESTAMP '2024-05-07 14:09:03'.

Do not rely on WHERE hire_date = '07-MAY-24'. Oracle then uses the session's NLS_DATE_FORMAT to read the text, so the same query behaves differently for another user. Always call TO_DATE with a format or use a DATE literal.

Oracle 12.2 added forgiving versions that return a default instead of an error:

SQL
TO_NUMBER('abc' DEFAULT 0 ON CONVERSION ERROR)     -- 0
TO_DATE('31-02-2024' DEFAULT NULL ON CONVERSION ERROR, 'DD-MM-YYYY')   -- NULL
VALIDATE_CONVERSION('abc' AS NUMBER)               -- 0 (cannot convert); 1 if it can

CAST(x AS type) is the standard form, e.g. CAST('42' AS NUMBER). TO_* functions give you format control; CAST is portable.

Handling NULL

NULL is the absence of a value, and most expressions with NULL return NULL. Oracle gives you these tools:

Function Meaning
NVL(a, b) b if a is NULL, else a
NVL2(a, b, c) b if a is not NULL, else c
COALESCE(a, b, c, ...) The first non-NULL argument (ANSI standard, any number of arguments)
NULLIF(a, b) NULL if a = b, else a
SQL
SELECT last_name, NVL(commission_pct, 0) AS commission FROM employees;
SELECT last_name, NVL2(commission_pct, 'Sales', 'Staff') AS kind FROM employees;
SELECT COALESCE(mobile, office_phone, home_phone, 'no phone') FROM contacts;
SELECT total / NULLIF(quantity, 0) AS unit_price FROM order_lines;   -- avoids ORA-01476 divide by zero

NVL evaluates both arguments; COALESCE stops at the first non-NULL ("short circuit"), which matters when the second argument is an expensive function call. NVL also converts the second argument to the first's type, while COALESCE requires the types to match. Prefer COALESCE in new code; you will still read NVL everywhere.

Stand-ins: NVL is ifnull or coalesce, and NVL2 is a CASE:

SQL Stand-ins for NVL, NVL2 and NULLIF ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

CASE and DECODE

CASE is the standard way to branch inside a query. It has two forms.

SQL
-- Simple CASE: compares one expression with values
CASE department_id WHEN 10 THEN 'Eng' WHEN 20 THEN 'Design' ELSE 'Other' END

-- Searched CASE: each WHEN is its own condition
CASE WHEN salary >= 100000 THEN 'high'
     WHEN salary >= 60000  THEN 'mid'
     ELSE 'entry' END

DECODE is Oracle's older function for the same job:

SQL
DECODE(department_id, 10, 'Eng', 20, 'Design', 'Other')

Its odd feature is that DECODE(x, NULL, 'none', 'some') treats NULL = NULL as a match, which CASE x WHEN NULL does not. Otherwise CASE is clearer, handles ranges and works in PL/SQL as well.

SQL Searched CASE (works unchanged in Oracle) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Implicit conversion: a warning

SQL
SELECT * FROM employees WHERE employee_id = '100';   -- number column, text literal

Oracle converts the text '100' to a number, which works. The reverse, a text column compared with a number, converts each row's column. That defeats indexes and raises ORA-01722: invalid number the day a non-numeric value appears. Make types match yourself.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does TO_CHAR(DATE '2024-05-07', 'YYYY-MM-DD') return?

    TO_CHAR returns text formatted by the model; a DATE value is only shown in this form by the client.
  2. 2. Which returns the first non-NULL value from a list of any length?

    COALESCE accepts any number of arguments and returns the first one that is not NULL. NVL takes exactly two.
  3. 3. How do you avoid a divide-by-zero error in total / qty when qty may be 0?

    NULLIF(qty, 0) returns NULL when qty is 0, and any division by NULL yields NULL instead of an error.
  4. 4. Why is WHERE hire_date = '07-MAY-24' a bad idea?

    Implicit conversion reads the text using session settings. Use DATE '2024-05-07' or TO_DATE with a format.
  5. 5. What does NVL2(x, 'a', 'b') return when x is not NULL?

    NVL2 returns the second argument when x is not NULL and the third when x is NULL.

Summary

  • Convert explicitly with TO_CHAR, TO_DATE, TO_NUMBER and CAST, using format models; FM strips padding.
  • Use DATE '...' and TIMESTAMP '...' literals, never rely on NLS_DATE_FORMAT.
  • NVL, NVL2, COALESCE and NULLIF handle NULL; COALESCE is standard and short-circuits.
  • CASE branches on conditions; DECODE is the older Oracle-only form.
  • Implicit conversions can hide bugs and defeat indexes.

Next: Aggregate and Analytic Functions