1Z0-149: Using Single-Row Functions to Customize Output

What this domain tests

Single-row functions return one result per input row. The exam tests character, numeric, date, and general functions, including nesting and NULL behavior.

Core rules

• Character: UPPER, LOWER, INITCAP change case; LENGTH counts characters; SUBSTR(str, start, length) supports negative starts; INSTR returns the position or 0; LPAD/RPAD pad or truncate; TRIM, LTRIM, RTRIM, and REPLACE edit strings.
• Numeric: ROUND, TRUNC, MOD, ABS, CEIL, FLOOR, POWER. ROUND and TRUNC accept a precision that may be negative.
• Date: SYSDATE returns current date and time. Date arithmetic uses days. MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC operate on dates.
• General: NVL, NVL2, NULLIF, COALESCE, CASE, and DECODE.
• Most functions propagate NULL; the general functions are the usual exceptions.

Worked example

SELECT last_name, UPPER(SUBSTR(last_name, 1, 3)) AS prefix, ADD_MONTHS(hire_date, 6) AS review_date FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date) > 120;

SUBSTR extracts the first three characters, and UPPER makes them uppercase. ADD_MONTHS adds six calendar months; if the target month is shorter, Oracle returns the last day of that month. MONTHS_BETWEEN returns a fractional number, so the comparison is numeric.

Traps

• Assuming SUBSTR with a negative start counts from the right but still reads forward; the direction changes.
• Forgetting that INSTR returns 0, not NULL, when not found.
• Using TRIM with the wrong syntax; it trims a character, not a string pattern.
• Expecting NVL to change the data type of the first argument; it uses the first argument's type.
• Adding a number to a date expecting hours; date arithmetic is in days.