Conversion Functions and Conditional Expressions
What the domain covers
Conversion functions change data types explicitly; conditional functions replace nulls or choose values. This domain tests TO_CHAR, TO_DATE, TO_NUMBER, NVL, NVL2, NULLIF, COALESCE, CASE, and DECODE.
Core rules
- Explicit conversion is safer than implicit conversion, which depends on session NLS settings.
- TO_CHAR(number or date, format) formats output; TO_DATE(char, format) parses input; TO_NUMBER(char) converts numeric text.
- Output case follows the format element's case, such as MON versus Mon.
- NVL(expr1,expr2) returns expr2 if expr1 is null; both should have compatible types.
- NVL2(expr1,expr2,expr3) returns expr2 if expr1 is not null, else expr3.
- NULLIF(a,b) returns null if a=b, else a. COALESCE returns the first non-null argument.
- CASE is ANSI-style and supports searched conditions; DECODE is Oracle-specific, tests equality, and treats two nulls as equal.
Worked example
SELECT last_name, TO_CHAR(hire_date,'YYYY-MM') AS hire_month, CASE WHEN commission_pct IS NULL THEN 'None' ELSE TO_CHAR(commission_pct) END AS commission FROM employees;
Reasoning: TO_CHAR formats the date into year-month text. CASE checks null explicitly because commission_pct = NULL is unknown. The ELSE branch converts the number to character so both CASE result branches are character data.
Traps
Do not compare NULL with =. NVL may implicitly convert, but explicit conversion avoids surprises. TO_DATE with a two-digit year uses RR semantics, not simply 20xx. DECODE cannot do range tests. COALESCE can take many arguments; NVL takes exactly two.