5 Functions

 5.1 Numeric, Character, and Date Functions

• ABS(n) - Absolute value

 
• ACOS(n) - Arc cosine of n

 
• ASIN(n) - Arc sine of n

 
• ATAN(n) - Arc tan of n

 
• CEIL(n) - Ceiling

 
• COS(n) - Cosine of an angle in radians (and there are other trigonometric functions beyond what we've shown here)

 
• EXP(n) - returns e to the n^th power

 
• FLOOR(n) - Floor

 
• LN(n) - Natural logarithm

 
• LOG(m, n) - Logarithm of n with base m

 
• MOD(m, n) - Modulus function

 
• POWER(m, n) - Returns m raised to the n^th power

 
• ROUND(n [, m]) - Returns n rounded to m places in front of the decimal, 0 places if m is omitted

 
• ROUND(d, fmt) - Rounds date to the unit specified by the format string

 
• SIGN(n) - if n < 0, returns -1; if n > 0, returns 1

 
• SQRT(n) - Square root function

 
• TRUNC (n, [, m]) - Numeric truncation function

 
• TRUNC (d, fmt) - Truncates date to the unit specified by the format string

 
• CHR(n) - Returns the character having the binary equivalent to n in the database character set

 
• CONCAT(char1, char2) - Concatenation; similar to ||

 
• INITCAP(char) - Returns char with the first letter of each word capitalized

 
• LOWER(char) - Returns char with all letters in lower case

 
• SUBSTR(char, m[, n]) - Substring function

 
• UPPER(char) - Returns char with all letters in upper case

 
• ASCII(char) - ASCII value of char

 
• LENGTH(char) - Length of char

 
• ADD_MONTHS(d, n) - Returns the date d plus n months

 
• LAST_DAY(d) - Returns the last day of the month that contains d

 
• MONTHS_BETWEEN(d1, d2) - Returns the number of months between dates d1 and d2

 
• NEXT_DAY(d, char) - The next weekday after d that is called char [e.g., if char is "Friday", then this would return the first Friday after date d]

 
• SYSDATE - Current date and time

5.2 Group Functions

• AVG - Average

 
• COUNT - Count

 
• MAX - Maximum

 
• MIN - Minimum

 
• STDDEV - Standard Deviation

 
• SUM - Sum

 
• VARIANCE - Variance

5.3 Functions for Conversions

All 4 of these functions are listed below. d, char, and n is what you're converting from, and fmt is the optional format. For dates, leaving out fmt means that the system default date format is used (the default date format is DD-MON-YY e.g. 15-MAY-01).  The Oracle date format actually includes both a date and a time component.  If you do not specify a time component (you'll see later how to specify times), then the time component is set to 12:00 A.M.  If you only specify a time component, then the date component is set to the current date.

For the conversion from numbers to characters, the output will just be a VARCHAR2 big enough to hold the numbers if you leave out fmt.
 

• TO_CHAR(d, [,fmt]) - converts DATE to VARCHAR2

 
• TO_DATE(char, [,fmt]) - converts CHAR or VARCHAR2 to DATE

 
• TO_CHAR(n [,fmt]) - converts NUMBER to VARCHAR2

 
• TO_NUMBER(char [,fmt]) - converts CHAR or VARCHAR2 to NUMBER

Although Oracle automatically converts character values that are in the default date format to date values, it is best to be explicit and use the TO_DATE function wherever a date value is expected.
 

For NUMBERs, here are some format elements (fmt):
 

• 9 - specifies the number of significant digits returned (e.g., 9999)

 
• 0 - returns a leading 0 as a 0 rather than a blank (e.g., 0999 or 9990)

 
• $ - prefixes the value with a dollar sign (e.g., $9999)

 
• MI - returns "-" after negative values or a trailing space for positive values (e.g., 9999MI)

 
• S - returns "+" for positive values and "-" for negative values in this position (e.g., S9999)

 
• PR - returns negative values in angled brackets. For positive values, a leading and trailing space is returned (e.g., 9999PR)

 
• , - returns a comma in this position (e.g., 9,999)

 
• . - returns a period in this position, separating the integral and fractional parts of a number (e.g., 99.99)

 
• V - multiplies the value by 10^n, where n is the number of 9's after the "V" (e.g., 999V99)

 
• EEEE - returns the value in scientific notation (e.g., 9.999EEEE)

Note:  There is no need to specify a negative sign because it is automatically placed in front of a number if the number is negative.
 

For DATEs, here are some of the format elements (fmt):
 

• SCC or CC - century ("S" means to prefix BC dates with "-")

 
• YYYY - 4 digit year

 
• YYY or YY or Y - last 3, 2, or 1 digit of year

 
• Y,YYYY - year with comma in this position

 
• SYEAR or YEAR - year spelled out ("S" means to prefix BC dates with "-")

 
• BC or AD - BC/AD indicator

 
• B.C. or A.D. - BC/AD indicator with periods

 
• Q - quarter of year (either 1, 2, 3, or 4)

 
• MM - month (from 01 to 12)

 
• RM - Roman numeral month (from I to XII)

 
• MONTH - name of month, padded with blanks to length of 9 characters

 
• MON - abbreviated name of month

 
• WW - week of year (from 1 to 53)

 
• W - week of month (from 1 to 5)

 
• DDD - day of year (from 1 to 366)

 
• DD - day of month (from 1 to 31)

 
• D - day of week (from 1 to 7)

 
• DAY - name of day, padded with blanks to 9 characters

 
• DY - abbreviated name of day

 
• AM or PM - meridian indicator

 
• A.M. or P.M. - meridian indicator with periods

 
• HH or HH12 - hour of day (from 1 to 12)

 
• HH24 - hour of day (from 0 to 23)

 
• MI - minute (from 0 to 59)

 
• SS - second (from 0 to 59)

 
• SSSSS - seconds past midnight (from 0 to 86399)

 
• - / , . ; : 'text' - all these will be duplicated in the output, as is

 
• TH - added to the end to indicate ordinal number (e.g., DDTH gives "4th")

 
• SP - added to the end to give a spelled out number (i.e. DDSP gives "FOUR") [can be combined with TH]

5.4  Date Examples

EX 1.

SELECT SYSDATE
FROM DUAL;

Returns

SYSDATE
-------------
15-MAY-01

This returns the current date in the default date format.  Dual is just a dummy table.

EX 2.

SELECT TO_CHAR(SYSDATE, 'Dy fmMonth DD HH:MI:SS') AS "Current Date and Time"
FROM DUAL;

Returns

Current Date and Time
---------------------
Thu May 17 3:39:22

This example shows how to use TO_CHAR() to control the output format of the current date.  The fm in the format string is a format modifier that removes blank padding and leading zeros in the return value of TO_CHAR.  For dates, right padding in character elements such as MONTH and leading zeros of number elements such as HH are removed.  For example, without the fm in the above statement the output would be

Current Date and Time
-------------------------
Thu May       17 03:39:22

The scope of fm is the rest of the format string following the fm.  Each subsequent occurence of fm toggles its effect.

Capitalization in the format string can affect the output of the date.  For example, if the above format string was
'DY fmMONTH DD HH:MI:SS', the output would be

Current Date and Time
------------------------
THU MAY 17 3:39:22

EX 3.

INSERT INTO calender
VALUES (TO_DATE('2001/05/15', 'YYYY/MM/DD'));

This inserts the given date into the table named calender.  This does not store 15-MAY-01 as 2001/05/15 in the calender table.  For example, typing

SELECT * FROM calender

Returns

The Date
-------------
15-MAY-01

EX 4.

SELECT (SYSDATE - 1) AS "Yesterday"
FROM DUAL;

Returns

Yesterday
---------
16-MAY-01

You can add and subtract constants to and from dates.  The constants are treated as days.  To add months to a date, you can use the ADD_MONTHS function.  You can also subtract dates to and from each other.  This returns the number of days between the dates.  To find the number of months between dates, you can use the MONTHS_BETWEEN function.  The standard comparison operators such as <, =, <>, != (<> is the same as !=), <=, etc. can be used with dates.

5.5  Number Example

EX 1.

SELECT TO_CHAR(999.0258, 'fm$9,990.99') AS Salary
FROM DUAL;

Returns

SALARY
----------
$999.03

The fm in the format string is a format modifier for TO_CHAR() that removes blanks to the left of the number so that the number appears left aligned.  It is ok for the format string to be longer than the actual number at the left or right of the decimal point.  However, if the format string is shorter than the number at the left of the decimal point, a string of '###...' would be returned.

For more information on Oracle's built-in functions, refer to the "Functions" chapter in the Oracle SQL Reference.  For more information on date and number format elements, refer to the "Basic Elements of Oracle SQL" chapter in the same reference.