Functions in SQL

General Functions:
NVL, NVL2


Character Functions: 
Character functions accept character data as an input and can return both character and numeric values. Character functions can be divided into case-conversion and character manipulation types.
LOWER, UPPER, INITCAP are the three case conversion functions. 
Some Character -manipulations functions are : CONCAT, SUBSTR, INSTR, LENGTH, LPAD, TRIM
Number functions:
Number functions accept numeric input and return numeric input.
ROUND: rounds to the nearest unit of 100.
TRUNC: truncates the column, or value to n decimal places.
MOD: finds the reminder of the first argument divided by second argument. It is frequently used to determine whether a value is odd or even.
Date related functions:
Oracle's default date format is : DD-MON-RR. RR date is similar to YY but it also specify century. RR determines the default value for century in INSERT.
SYSDATE function:
 Returns current database server date and time. If your database in London and you are in California, it will display London current time. If you want California time use - CURRENT_DATE function. You can use arithmetic operators with SYSDATE function.
 Date-Manipulation functions : MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY,  ROUND, TRUNC, and LAST_DAY
Date function can be combined with Arithmetic function: date+number, Date-number, date-date, Date +number/24.
Data Type Conversion: 
TO_NUMBER, TO_DATE, TO_CHAR, TO_DATE 
Nesting Functions:
Nested functions are evaluated from deepest to the least deep level.
 Group functions : 
Group functions operates in multiple rows and gives a one result per group. Group functions ignores null values.  AVG, SUM, MIN, MAX, COUNT

    2 comments:

    Parasuraj said...

    Pls give some Examples for this functions. It'll help us to improve ourselves and more easy to understand.

    Parasuraj said...

    Pls give some examples for this functions, it'll help us to understand the concept very well.