The Oracle implementation of SQL provides a number of functions that can be used in SELECT statements. Functions are typically grouped into the following:

• Single row functions - Operate on column values for each row returned by a query.
• Group functions - Operate on a collection (group) of rows.

• Math functions include:

ABS (x) - Absolute Value of x
CEIL (x) - Smallest integer greater than or equal to x. COS (x) - Cosine of x
FLOOR (x) - Largest integer less than or equal to x. LOG (x) - Log of x
LN (x) - Natural Log of x
ROUND (x, n) - Round x to n decimal places to the right of the decimal point.
SIN (x) - Sine of x
TAN (x) - Tangent of x
TRUNC (x, n) - Truncate x to n decimal places to the right of the decimal point.
• Character functions include:

CHR (x) - Character for ASCII value x.
INITCAP (s) - String s with the first letter of each word capitalized.
LOWER (s) - Converts string s to all lower case letters.
LPAD (s, x) - Pads string s with x spaces to the left.
LTRIM (s) - Removes leading spaces from s.
REPLACE (s1, s2, s3) - Replace occurrences of s1 with s2 in string s.
RPAD (s, x) - Pads string s with x spaces to the right.
RTRIM (s) - Removes trailing spaces from s.
SUBSTR (s, x1, x2) - Return a portion of string s starting at position x1 and ending with position x2. If x2 is omitted, it's value defaults to the end of s.
UPPER (s) - Converts string s to all upper case letters.
• Character functions that return numbers include:

ASCII (c) - Returns the ASCII value of c
INSTR (s1, s2, x) - Returns the position of s2 in s1 where the search starts at position x.
LENGTH (s) - Length of s
• Conversion functions include:

TO_CHAR (date, format) - Converts a date column to a string of characters. format is a set of Date formatting codes where:
YYYY is a 4 digit year.
NM is a month number.
MONTH is the full name of the month.
MON is the abbreviated month.
DDD is the day of the year.
DD is the day of the month.
D is the day of the week.
DAY is the name of the day.
HH is the hour of the day (12 hour clock)
HH24 is the hour of the day (24 hour clock)
MI is the minutes.
SS is the seconds.

TO_CHAR (number, format) - Converts a numeric column to a string of characters. format is a set of number formatting codes where:
9 indicates a digit position. Blank if position value is 0.
0 indicates a digit position. Shows a 0 if the position value is 0.
$ displays a leading currency indicator.
TO_DATE (s, format) - Converts a character column (string s to a date. format is a set of Date formatting codes as above.
TO_NUMBER (s, format) - Converts a character column (string s to a Number. format is a set of Number formatting codes as above.

• Date functions include:

SYSDATE - Returns the current date (and time if the TO_CHAR function is used) from the system clock.
• Some additional function are:

DECODE (s, search1, result1, search2, result2) - Compares s with search1, search2, etc. and returns the corresponding result when there is a match.

NVL (s, expression) - If s is NULL, return expression. If s is not null, then return s.

USER - Returns the username of the current user.

• AVG (col) - Returns the average of a group of rows for col
• MAX (col) - Returns the maximum of a group of rows for col
• MIN (col) - Returns the minimum of a group of rows for col
• STDEV (col) - Returns the standard deviation of a group of rows for col
• SUM (col) - Returns the sum (total) of a group of rows for col
• VARIANCE (col) - Returns the variance of a group of rows for col

• COUNT (columns) - Returns the number of instances of a group of rows for (columns)

Examples of functions are given in the following section.

Exercise 7: Functions

• Display the average, minimum, and maximum grade point average for all of the students
• For each student, write a sentence like the following:

Congratulations Bill, your grade point average is 3.45
You'll need to use the TO_CHAR function to convert the GPA column (which is a NUMBER data type) to a set of characters.
• For each student, count the number of courses he or she has taken.
• Modify the above query to only count CIS courses.

Hint: You'll need to use the SUBSTR function on the COURSENUMBER column to extract the first three letters. Then compare this to 'CIS'.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips