Single-Row Functions
Domain 3 tests functions that return one result per input row. You need to choose the right character, numeric, date, and general function and predict its return value, including behavior with NULL.
Core rules
- Character functions include UPPER, LOWER, INITCAP, SUBSTR, INSTR, LENGTH, LPAD, RPAD, TRIM, and REPLACE. SUBSTR positions start at 1; position 0 is treated like 1 in Oracle.
- Numeric functions include ROUND, TRUNC, and MOD. ROUND with a negative precision rounds left of the decimal point. TRUNC cuts toward zero. MOD returns the remainder.
- General functions include NVL, NVL2, COALESCE, NULLIF, and CASE. NVL replaces NULL with a substitute; COALESCE returns the first non-NULL argument.
- Most single-row functions return NULL when given NULL, but NVL, COALESCE, and related functions are designed to handle NULL.
- Applying a function to an indexed column can prevent ordinary index use unless a function-based index exists.
Worked example
SELECT employee_id, UPPER(SUBSTR(last_name,1,3)) AS code, ROUND(salary*1.1,-2) AS rounded FROM employees WHERE MOD(employee_id,2)=1;
SUBSTR takes the first three characters of last_name. UPPER changes them to uppercase. salary*1.1 applies a 10 percent increase. ROUND with -2 rounds to the nearest hundred. The WHERE keeps odd employee_id values because MOD returns the remainder after division by 2.
Traps
INSTR returns 0, not NULL, when the substring is not found. SUBSTR with a negative start position counts from the end of the string. TRIM syntax differs from REPLACE. ROUND and TRUNC with negative precision affect digits left of the decimal point. Assuming every function ignores NULL is wrong; verify each function.
Key points
- SUBSTR and INSTR positions are 1-based.
- ROUND can use negative precision.
- MOD is useful for odd/even filtering.
- NVL and COALESCE handle NULL explicitly.
- Functions on indexed columns can block index use.