Conversion Functions and Conditional Expressions
Conversion
Explicit conversion is safer and is what questions reward. TO_CHAR(value, 'format') turns numbers or dates into strings; TO_DATE(string, 'format') parses; TO_NUMBER(string) parses numerics. Format elements matter: YYYY, MM, DD, HH24:MI:SS, fm to suppress padding, 9 and 0 for numeric masks.
Implicit conversion happens when types are mixed - a string compared to a date, or a number concatenated. It depends on NLS settings and can raise ORA-01722 for invalid numbers. The RR format element resolves two-digit years into the nearest appropriate century, unlike YY which assumes the current century.
Conditionals
NVL(expr, replacement) replaces NULL. NVL2(expr, if_not_null, if_null) returns one of two values. NULLIF(a, b) returns NULL when a equals b, else a. COALESCE(a, b, c) returns the first non-null argument and takes any number of arguments. DECODE(expr, search1, result1, ..., default) is an equality-only mapping that treats two NULLs as equal. CASE comes in two forms: simple (CASE expr WHEN v THEN r ...) and searched (CASE WHEN condition THEN r ...), and it can be used in ORDER BY and inside aggregates.
Traps
NVL requires compatible data types and evaluates both arguments. COALESCE short-circuits but still needs at least two arguments. Remember that DECODE considers NULL equal to NULL while CASE/= does not.
Worked example
SELECT last_name, COALESCE(commission_pct, 0) AS comm, CASE WHEN salary > 10000 THEN 'HIGH' ELSE 'OTHER' END AS band FROM employees;