Single-Row Functions
What they do
Single-row functions run once per row and return one value per row. Learn the families, not every variant.
Character: UPPER, LOWER, INITCAP, LENGTH, SUBSTR(str, start, len), INSTR(str, sub), LPAD/RPAD, TRIM, REPLACE, CONCAT. Numeric: ROUND, TRUNC, MOD, ABS, CEIL, FLOOR. Date: SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, and ROUND/TRUNC applied to dates.
Details that get tested
SUBSTR with a start of 0 behaves like 1; a negative start counts from the end of the string. INSTR returns 0 when the substring is absent, never NULL. Subtracting two dates yields a number of days (fractional); adding a number to a date adds days. MONTHS_BETWEEN can return a fraction. LAST_DAY returns the final day of the month; NEXT_DAY returns the next occurrence of a named weekday strictly after the given date.
Functions nest: the inner result feeds the outer call, and the innermost executes first.
Worked example
SELECT SUBSTR(last_name, 1, 3), ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) FROM employees;
The first three characters of the name are returned, then full months of service are rounded to a whole number.
Rule of thumb
If a function ignores NULLs or returns a number instead of NULL, exam writers love it. Memorise: INSTR → 0, date difference → days, ADD_MONTHS handles month-end intelligently.