Single-Row Functions
What the domain covers
Single-row functions transform one row and return one result per row. This domain tests character, numeric, and date functions, including nesting.
Core rules
- Character functions include UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM, and REPLACE. SUBSTR positions start at 1; a negative start counts from the end.
- Numeric functions include ROUND, TRUNC, and MOD. ROUND(45.926,2)=45.93; TRUNC(45.926,2)=45.92.
- Date functions include SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ROUND, and TRUNC. Date + number adds days; date - date returns days.
- Functions can be nested; the inner function completes first.
- Single-row functions can appear in SELECT, WHERE, and ORDER BY and are evaluated per row.
Worked example
SELECT last_name, UPPER(SUBSTR(last_name,1,3)) AS prefix, ROUND(salary/12,2) AS monthly FROM employees;
Reasoning: SUBSTR extracts the first three characters because start is 1 and length is 3. UPPER changes those characters to uppercase. salary/12 divides before ROUND; ROUND with 2 keeps two decimal places. Each source row produces exactly one output row.
Traps
Do not expect SUBSTR to be zero-based. MONTHS_BETWEEN(date1,date2) returns date1 minus date2 in months, including a fraction; argument order matters. ROUND on a date rounds to the nearest day or month depending on format, while TRUNC truncates. Adding 1 to a date adds one day, not one month. Character functions return NULL when given NULL.