Functions in Oracle

Character Functions:

    1. SUBSTR    ( mainString, start, length )    
    2. INSTR( mainString, searchString, start, occurance)
    3. REPLACE( mainString, searchString, replaceString )
    4. TRANSLATE( mainString, charString, translateString )
More about Character Functions

Numeric Functions:

    1. ABS( number )
    2. SIGN( number )
    3. SQRT( number )
    4. POWER( number1,number2 )
    5. MOD( number1,number2 )
    6. CEIL( decimal_number )
    7. FLOOR( decimal_number )
    8. ROUND
        a) ROUND( decimal_number, number )
        b) ROUND( date, mode )
    9. TRUNC
        a) TRUNC( decimal_number, number )
        b) TRUNC( date, mode )
More about Numeric Functions

Date Functions:

    1. ADD_MONTHS( date, months )
    2. MONTHS_BETWEEN ( date1, date2 )
    3. EXTRACT( MODE from date )
    4. LAST_DAY( date )
    5. NEXT_DAY( date )
More about Date Functions

Conversion Functions:

    1. TO_CHAR( date, format )
    2. TO_CHAR( number, format )
    3. TO_DATE( string, format )
    4. TO_DATE( number, format )
    5. TO_NUMBER( string, format )
More about Conversion Functions

Special Functions:

    1. NVL( value1, value2);
    2. COALESCE( expr1, expr2, expr3, ..... )
    3. NVL2( expr1, expr2, expr3 )
    4. DECODE( expr1, value1, return value1, value2, return value2, ..... default value )
    5. GREATEST( value1, value2, value3, .... )
    6. LEAST( value1, value2, value3, .... )
    7. CHR( number )
    8. ASCII( character )
More about Special Functions

OLAP Functions or Analytical Functions or Window Functions:

    1. RANK() OVER ( expr )
    2. DENSE_RANK() OVER ( expr )
    3. ROW_NUMBER() OVER ( expr )
More about OLAP functions

Multi Row Functions:

    1. MAX( column )
    2. MIN( column )
    3. SUM( column )
    4. AVG( column )
    5. COUNT( column )
More about Multi Row Functions

Tips:

1. Combination of Multi Row Functions and Single Row Function is not possible in Select clause.
2. In Character Functions, LENGTH and INSTR returns a Number.
    In Date Functions, MONTHS_BETWEEN and EXTRACT( year, day ) retuns a Number.
3. All the above Functions return NULL values except
    NVL, COALESCE, NVL2, REPLACE, CONCAT
4. Functions which doesn't accept any arguments are called 'PSEUDO CODE' functions.
    =>    Sequence Pseudo Columns { CURRVAL, NEXTVAL }
    =>    ROWID
    =>    ROWNUM
5. Valid and Invalid Operations
    =>    sysdate + 10                is valid.
    =>    '01-JAN-12' + 20            is In Valid.
    =>    sysdate + (sysdate + 10)        is In Valid.