Function | Input Argument | Value Returned |
---|---|---|

ABS ( m ) | m = value | Absolute value of m |

MOD ( m, n ) | m = value, n = divisor | Remainder of m divided by n |

POWER ( m, n ) | m = value, n = exponent | m raised to the nth power |

ROUND ( m [, n ] ) | m = value, n = number of decimal places, default 0 | m rounded to the nth decimal place |

TRUNC ( m [, n ] ) | m = value, n = number of decimal places, default 0 | m truncated to the nth decimal place |

SIN ( n ) | n = angle expressed in radians | sine (n) |

COS ( n ) | n = angle expressed in radians | cosine (n) |

TAN ( n ) | n = angle expressed in radians | tan (n) |

ASIN ( n ) | n is in the range -1 to +1 | arc sine of n in the range -π/2 to +π/2 |

ACOS ( n ) | n is in the range -1 to +1 | arc cosine of n in the range 0 to π |

ATAN ( n ) | n is unbounded | arc tangent of n in the range -π/2 to + π/2 |

SINH ( n ) | n = value | hyperbolic sine of n |

COSH ( n ) | n = value | hyperbolic cosine of n |

TANH ( n ) | n = value | hyperbolic tangent of n |

SQRT ( n ) | n = value | positive square root of n |

EXP ( n ) | n = value | e raised to the power n |

LN ( n ) | n > 0 | natural logarithm of n |

LOG ( n2, n1 ) | base n2 any positive value other than 0 or 1, n1 any positive value | logarithm of n1, base n2 |

CEIL ( n ) | n = value | smallest integer greater than or equal to n |

FLOOR ( n ) | n = value | greatest integer smaller than or equal to n |

SIGN ( n ) | n = value | -1 if n < 0, 0 if n = 0, and 1 if n > 0 |

Here are some examples of the use of some of these numeric functions:

select round (83.28749, 2) from dual; select sqrt (3.67) from dual; select power (2.512, 5) from dual;

Function | Input Argument | Value Returned |
---|---|---|

INITCAP ( s ) | s = character string | First letter of each word is changed to uppercase and all other letters are in lower case. |

LOWER ( s ) | s = character string | All letters are changed to lowercase. |

UPPER ( s ) | s = character string | All letters are changed to uppercase. |

CONCAT ( s1, s2 ) | s1 and s2 are character strings | Concatenation of s1 and s2. Equivalent to s1 || s2 |

LPAD ( s1, n [, s2] ) | s1 and s2 are character strings and n is an integer value | Returns s1 right justified and padded left with n characters from s2; s2 defaults to space. |

RPAD ( s1, n [, s2] ) | s1 and s2 are character strings and n is an integer value | Returns s1 left justified and padded right with n characters from s2; s2 defaults to space. |

LTRIM ( s [, set ] ) | s is a character string and set is a set of characters |
Returns s with characters removed up to the first character not in set; defaults to space |

RTRIM ( s [, set ] ) | s is a character string and set is a set of characters |
Returns s with final characters removed after the last character not in set; defaults to space |

REPLACE ( s, search_s [, replace_s ] ) | s = character string, search_s = target string, replace_s = replacement string | Returns s with every occurrence of search_s in s replaced by replace_s; default removes search_s |

SUBSTR ( s, m [, n ] ) | s = character string, m = beginning position, n = number of characters | Returns a substring from s, beginning in position m and n characters long; default returns to end of s. |

LENGTH ( s ) | s = character string | Returns the number of characters in s. |

INSTR ( s1, s2 [, m [, n ] ] ) | s1 and s2 are character strings, m = beginning position, n = occurrence of s2 in s1 | Returns the position of the nth occurrence of s2 in s1, beginning at position m, both m and n default to 1. |

Here are some examples of the use of String functions:

select concat ('Alan', 'Turing') as "NAME" from dual; select 'Alan' || 'Turing' as "NAME" from dual; select initcap ("now is the time for all good men to come to the aid of the party") as "SLOGAN" from dual; select substr ('Alan Turing', 1, 4) as "FIRST" from dual;

Function | Input Argument | Value Returned |
---|---|---|

NANVL ( n2, n1 ) | n1, n2 = value | if (n2 = NaN) returns n1 else returns n2 |

TO_CHAR ( m [, fmt ] ) | m = numeric value, fmt = format | Number m converted to character string as specified by the format |

TO_NUMBER ( s [, fmt ] ) | s = character string, fmt = format | Character string s converted to a number as specified by the format |

Symbol | Explanation |
---|---|

9 | Each 9 represents one digit in the result |

0 | Represents a leading zero to be displayed |

$ | Floating dollar sign printed to the left of number |

L | Any local floating currency symbol |

. |
Prints the decimal point |

, |
Prints the comma to represent thousands |

Function | Input Argument | Value Returned |
---|---|---|

AVG ( [ DISTINCT | ALL ] col ) | col = column name | The average value of that column |

COUNT ( * ) | none | Number of rows returned including duplicates and NULLs |

COUNT ( [ DISTINCT | ALL ] col ) | col = column name | Number of rows where the value of the column is not NULL |

MAX ( [ DISTINCT | ALL ] col ) | col = column name | Maximum value in the column |

MIN ( [ DISTINCT | ALL ] col ) | col = column name | Minimum value in the column |

SUM ( [ DISTINCT | ALL ] col ) | col = column name | Sum of the values in the column |

CORR ( e1, e2 ) | e1 and e2 are column names | Correlation coefficient between the two columns after eliminating nulls |

MEDIAN ( col ) | col = column name | Middle value in the sorted column, interpolating if necessary |

STDDEV ( [ DISTINCT | ALL ] col ) | col = column name | Standard deviation of the column ignoring NULL values |

VARIANCE ( [ DISTINCT | ALL ] col ) | col = column name | Variance of the column ignoring NULL values |

Function | Input Argument | Value Returned |
---|---|---|

ADD_MONTHS ( d, n ) | d = date, n = number of months | Date d plus n months |

LAST_DAY ( d ) | d = date | Date of the last day of the month containing d |

MONTHS_BETWEEN ( d, e ) | d and e are dates | Number of months by which e precedes d |

NEW_TIME ( d, a, b ) | d = date, a = time zone (char), b = time zone (char) | The date and time in time zone b when date d is for time zone a |

NEXT_DAY ( d, day ) | d = date, day = day of the week | Date of the first day of the week after d |

SYSDATE | none | Current date and time |

GREATEST ( d1, d2, ..., dn ) | d1 ... dn = list of dates | Latest of the given dates |

LEAST ( d1, d2, ..., dn ) | d1 ... dn = list of dates | Earliest of the given dates |

Function | Input Argument | Value Returned |
---|---|---|

TO_CHAR ( d [, fmt ] ) | d = date value, fmt = format for string | The date d converted to a string in the given format |

TO_DATE ( s [, fmt ] ) | s = character string, fmt = format for date | String s converted to a date value |

ROUND ( d [, fmt ] ) | d = date value, fmt = format for string | Date d rounded as specified by the format |

TRUNC ( d [, fmt ] ) | d = date value, fmt = format for string | Date d truncated as specified by the format |

Format Code | Description | Range of Values |
---|---|---|

DD | Day of the month | 1 - 31 |

DY | Name of the day in 3 uppercase letters | SUN, ..., SAT |

DAY | Complete name of the day in uppercase, padded to 9 characters | SUNDAY, ..., SATURDAY |

MM | Number of the month | 1 - 12 |

MON | Name of the month in 3 uppercase letters | JAN, ..., DEC |

MONTH | Name of the month in uppercase padded to a length of 9 characters | JANUARY, ..., DECEMBER |

RM | Roman numeral for the month | I, ..., XII |

YY or YYYY | Two or four digit year | 71 or 1971 |

HH:MI:SS | Hours : Minutes : Seconds | 10:28:53 |

HH 12 or HH 24 | Hour displayed in 12 or 24 hour format | 1 - 12 or 1 - 24 |

MI | Minutes of the hour | 0 - 59 |

SS | Seconds of the minute | 0 - 59 |

AM or PM | Meridian indicator | AM or PM |

SP | A suffix that forces the number to be spelled out. | e.g. TWO THOUSAND NINE |

TH | A suffix meaning that the ordinal number is to be added | e.g. 1st, 2nd, 3rd, ... |

FM | Prefix to DAY or MONTH or YEAR to suppress padding | e.g. MONDAY with no extra spaces at the end |

Here are some examples of the use of the Date functions:

select to_char ( sysdate, 'MON DD, YYYY' ) from dual; select to_char ( sysdate, 'HH12:MI:SS AM' ) from dual; select to_char ( new_time ( sysdate, 'CDT', 'GMT'), 'HH24:MI' ) from dual; select greatest ( to_date ( 'JAN 19, 2000', 'MON DD, YYYY' ), to_date ( 'SEP 27, 1999', 'MON DD, YYYY' ), to_date ( '13-Mar-2009', 'DD-Mon-YYYY' ) ) from dual; select next_day ( sysdate, 'FRIDAY' ) from dual; select last_day ( add_months ( sysdate, 1 ) ) from dual;