Oracle Certification - SQL Fundamentals I - Single Row Functions

Single Row Functions

Single row functions are executed for every row in the result set, and can take zero or more arguments. This concept, is tested in the exam.

Character Functions

LOWER(string)

UPPER(string)

INITCAP(string)

LENGTH(string)

CONCAT(string1,string2)

RPAD(string,n,padding_char)

LPAD(string,n,padding_char)

SUBSTR(string,position,substring_length)

If position is specified as 0 it is assumed to be 1. A positive position causes Oracle to count from the first char to find the start position, and negative position counts from the end. If substring_length is omitted Oracle returns all characters until the end of the string, if less then 1 null.

INSTR(string,substring,position,occurrence)

INSTR searches string for substring. Oracle starts the search from position in the string, if positive from the beginning or negative from the end. The occurrence specified the number of occurrences of the substring that are searched for.

If the substring is found then an integer representing the first character of that string is returned. Otherwise zero is returned.

TRIM(trim_char|LEADING|TRAILING|BOTH FROM string)

REPLACE(string,search_string,replace_string)

Numeric Functions

ROUND(n,integer)

ROUND returns n rounded to integer places to the right of the decimal point. If n is negative then n is rounded to the left of the decimal point. If integer is omitted it is assumed to be zero.

TRUNC(n1,n2)

TRUNC returns n1 truncated to n2 decimal places. If n2 is negative then n1 is rounded to the left of the decimal point. If n2 is omitted it is assumed to be zero.

MOD(n1,n2)

MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.

Date Functions

MONTHS_BETWEEN(date1,date2)

MONTHS_BETWEEN returns the number of months between date1 and date2. If date1 is later than date2 then the result is positive, and vice versa. The result is fractional based on a 31 day calendar.

ADD_MONTHS(date,integer)

ADD_MONTHS returns date plus integer months. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.

LAST_DAY(date)

LAST_DAY returns the last day of the month that contains date.

NEXT_DAY(date,day_of_the_week)

NEXT_DAY returns the date on which the next day_of_the_week occurs after date.

TRUNC|ROUND(date,fmt)

TRUNC or ROUND returns date rounded to the nearest precision fmt given.

There is going to close attention paid to all of these functions, both on their syntax and operation.

Next Conversion Functions.