Using Single-Row and Conversion Functions
Using Single-Row and Conversion Functions
This domain tests functions that return one result for each input row. They can appear in SELECT, WHERE, and ORDER BY, and can be nested. The exam emphasizes character, numeric, date, conversion, and general functions, plus the difference between explicit and implicit conversion.
Core rules
- Character functions include UPPER, LOWER, INITCAP, SUBSTR, INSTR, LENGTH, LPAD, RPAD, TRIM, REPLACE, and concatenation with ||.
- Numeric functions include ROUND, TRUNC, MOD, CEIL, FLOOR, and ABS. ROUND and TRUNC take an optional precision, including negative values for digits left of the decimal point.
- Date functions include SYSDATE, CURRENT_DATE, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, and EXTRACT. Date arithmetic uses days; date + number returns a date.
- Conversion functions include TO_CHAR, TO_DATE, and TO_NUMBER. Format models control output and parsing; explicit conversion is safer and avoids unexpected implicit conversion.
- General functions include NVL, NVL2, NULLIF, COALESCE, CASE, and DECODE. NVL replaces null with a value; COALESCE returns the first non-null argument.
Worked example
SELECT last_name, NVL(TO_CHAR(commission_pct), 'No commission') AS comm, ROUND(salary * 1.1) AS raised FROM employees WHERE hire_date >= TO_DATE('01-JAN-2020','DD-MON-YYYY');
TO_CHAR converts the numeric commission to text so NVL can return a character value. ROUND applies to each row's raised salary. TO_DATE parses the literal using the supplied format, preventing reliance on the session date format.
Traps
NVL arguments must be type-compatible; mixing numeric and character values can cause implicit conversion. DECODE treats two nulls as equal, while CASE uses normal null comparison unless IS NULL is written. TO_CHAR number masks do not change stored precision. Applying a function to an indexed column can prevent ordinary index use.