Func Part..2

FUNCTION CONTINUES

NUMBER FUNCTIONS::

1.ROUND()
2.TRUNC()
3.MOD()
4.POWER()

1.ROUND()::

The ROUND() Function mainly used for Round of the Decimal Values Or Round of the Values based on the Position Which is
Passed in the argument.

Example::

**Using Second Argument As Postitive Values--ROUND(Arg1,Positive Value)::

SELECT ROUND(324.5456,2) From Dual;
O/P--324.54
SELECT ROUND(324.5456,3) From Dual;
O/P--324.546
SELECT ROUND(324.5,1) From Dual;
O/P--325
SELECT ROUND(324.5,0) From Dual;
O/P--325
SELECT ROUND(340.5456) From Dual;
O/P--340

**Using Second Argument As Negative Values--ROUND(Arg1,Negative Values)::

SELECT ROUND(324.5456,-1) From Dual;
O/P--320
SELECT ROUND(324.5456,-2) From Dual;
O/P--300
SELECT ROUND(1324.5,-3) From Dual;
O/P--1000
SELECT ROUND(326.5,-1) From Dual;
O/P--330
SELECT ROUND(374.5456,-2) From Dual;
O/P--400
SELECT ROUND(2674.5456,-3) From Dual;
O/P--3000
TRUNC()::

The TRUNC() function mainly used for "Truncate" or "Remove" the Values(From Position) based on the Position
we mentioned in the second argument.

SELECT TRUNC(324.5456,2) From Dual;
O/P--324.54
SELECT TRUNC(324.5456,3) From Dual;
O/P--324.545
SELECT TRUNC(324.5,1) From Dual;
O/P--324.1
SELECT TRUNC(324.5,0) From Dual;
O/P--324
SELECT TRUNC(340.5456) From Dual;
O/P--340

MOD()::

The MOD() function mainly used for Divid the Argument1/Arg2 and Returns the remainter value of the Statement.

Example::

SELECT MOD(3,2) FROM dual;
O/P--1

POWER()::

The POWER() function mainly used for multiply the argumnet1 with arg2(Number Of Times) and ruturn the Output

SELECT POWER(3,4) FROM dual;
O/P--3*3*3*3====>81

DATE FUNCTION::


There are four DATE functions are there.
1.NONTHS_BETWEEN()
2.ADD_MONTHS()
3.NEXT_DAY()
4.LAST_DATE()

"SYSDATE" is an inbuilt Pseudo function ,It mainly used for Getting Current Server date.(01-Apr-16)

SELECT SYSDATE from dual:
O/P--01-Apr-16
SELECT SYSDATE+10 from dual;
O/P--10-Apr-16
SELECT SYSDATE-10 from dual;
O/P--20-Mar-16
*We can Perform Add ,Sub with "SYSDATE"

Condition::

1.Date + Number = Date
2.Date - Number = Date
3.Date - Date = Number
4.Date + Date = NOT POSSIBE

(DATE)-(DATE) Example::

We Can Subtract One date from another Date using
 TO_Date("Your Date","Format DD-Mon-YY")

Example::


SELECT TO_DATE('05-Apr-16','DD-Mon-YY') - TO_DATE('01-Apr-16','DD-Mon-YY') as "RESULT" FROM dual;
O/P--4

i)MONTHS_BETWEEN()::

The MONTHS_BETWEEN() is mainly used for return the number of months between from Month(Arg1) to till Month(Arg2)

SELECT MONTH_BETWEEN(SYSDATE,'01-Jun-15') FROM dual;
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,'01-Jun-15')) FROM dual;
O/P--10

ii)ADD_MONTHS()

The ADD_MONTHS() increment the the Month(Arg1,Increment Month(+ Or -)

SELECT ADD_MONTHS(SYSDATE,3) FROM dual;
O/P--05-JUL-16
SELECT ADD_MONTHS(SYSDATE,-7) FROM dual;
O/P--05-SEP-15

NEXT_DAY()::

The NEXT_DAY() function is used for return the next date From(Argument 1).

SELECT NEXT_DAY(SYSDATE,'Thursday') FROM Dual;
O/P --NEXT Thursday Date--->14-Apr-16
SELECT NEXT_DAY(NEXT_DAY(SYSDATE,'FRIDAY'),'FRIDAY') FROM dual;
O/P ---Next 2nd Friday----->15-Apr-16

LAST_DAY()::

The LAST_DAY() function is used for returning the last day (Date) of the Month(Passed in the query)

SELECT LAST_DAY(SYSDATE) from dual;
O/P-->30-Apr-16

TYPE FUNCTIONS::

There are following three types types function are there,
1.TO_NUMBER()
2.TO_DATE()
3.TO_CHAR()


1.TO_NUMBER()::

TO_NUMBER() function is mainly used for converts a Character String into Number.

SELECT 5 + '5' FROM dual;--------Its an Implicit Conversion
O/P----10
SELECT 5 + TO_NUMBER('5') From dual;
O/P--->10

2.TO_DATE()::

The TO_DATE() function is mainly used for Converting the char & Other Unknown date format into a Oracle Standard DATE format.

For example--21-January-95 its a date format but oracle does not recognize the format, So in this case we can used TO_DATE()

SELECT TO_DATE('21-January-95','DD-Month-YY') From dual;
O/P---21-JAN-95

3.TO_CHAR()::

The TO_CHAR() function is mainly used for convert and displaying the Data base date format into used needed format.

SELECT TO_CHAR(SYSDATE,'DD Month Year') from dual;
O/P--->07 April Twenty Sixteen

3)i) Using ROMAN CHARACTER::

SELECT TO_CHAR(8,'RM') FROM dual;
O/P---viii

Example::
SELECT 'Due Date is' || TO_CHAR(SYSDATE,'DDspth month year') FROM dual;

O/P---Due Date is SEVENTH april twenty sixteen

Read More

Func Part..1

FUNCTIONS

FUNCTIONS::

1.Single Row Functions
2.Multi Row Functions

1.Single Row Functions::

If we input 'n' number of input and it produce 'n' number of Output.(n->n)

Types in Single Row Function::

1.Case Functions
   i)Case Manipulation
  ii)Character Manipulation

2.Number Functions
3.Date Functions
4.Type Functions
5.General Functions

1.Case Functions:

i)Case Manipulation:

There are three types of case manipulations are there,
1.UPPER()
2.LOWER()
3.INITCAP()

UPPER():

The UPPER() functions changes the selected colunn into UPPER CASE

Example::

SELECT UPPER(First_Name) FROM employees;
O/P---Arul Is table value after Using Upper the O/P is ARUL.

LOWER()::

The LOWER() functions changes the selected colunn into LOWER CASE

Example::

SELECT LOWER(First_Name) FROM employees;
O/P---Arul Is table value after Using LOWER the O/P is arul.

INITCAP()::

The INITCAP() function used for Changing the First Character into CAPS(UPPER)

SELECT INITCAP(First_Name) FROM employees;

ii)Character Manipulation::

1.LENGTH()
2.REVERSE()
3.CONCAT()
4.SUBSTR()
5.INSTR()
6.LPAD()  & RPAD()
7.TRIM() & LTRIM()  &  RTRIM()
8.REPLACE()
9.TRANSLATE()

LENGTH()::

The LENGTH() function is used for count the number of Char in the selected column including "space"

Select First_Name,LENGTH(First_Name) from employees;

O/P---Arul.....4

Using WHERE with LENGTH()::

SELECT * FROM employees where LENGTH(First_Name)=5;

REVERSE()::

The REVERSE() function is used for REVERSE the selected column "string"

SELECT REVERSE(First_Name) from employees;
  O/P--Arul.....lurA

CONCAT()::

The CONCAT() function is used for joining the two column.

SELECT CONCAT(First_Name,Salary) from employees;

O/P is----Arul15000

SUBSTR()::

The SUBSTR() function id used for returning the Values from Position to Count Position(From_Position,Number_Of_Pos)
                       ------------------------------------------------------------
SELECT First_Name,SUBSTR(First_Name,1,2) from employees;
          --Arul-----O/P is----Arul...Ar

SELECT First_Name,SUBSTR(First_Name,3,3) from employees;
         --Arulxa--O/P is-----Arulxa..lxa

SELECT First_Name,SUBSTR(First_Name,2) from employees;
         --Arulxa--O/P is-----Arulxa..rulxa

SELECT First_Name,SUBSTR(First_Name,-2,2) from employees;
         --Arulxa--O/P is-----Arulxa..xa(-2 Reverse 2 char count and next Print 2 Last Char)

SELECT First_Name,SUBSTR(1000-1,-2,2) from dual;
        --999,,,,,O/P Is 99

SELECT * from employees where SUBSTR(Hire_Date,-2,2)=89;
         --O/P Is 01-Jan-89)

INSTR()::

It returns the Position of the Given Char in the Selected Column

select first_name,INSTR(First_Name,('a')) from employees;
         ---arul----O/P is-----1

select first_name,INSTR(First_Name,('a')),INSTR(First_Name,('a'),1,2) from employees;
---arulxa----O/P is--1--6

LPAD() & RPAD()::

 The both functions mainly used for alignment.


SELECT First_Name,LPAD(First_Name,10,'0') FROM employees;
O/P is (If salary is 25000 -----0000025000)

SELECT First_Name,RPAD(First_Name,10,'$') FROM employees;
O/P is (If salary is 25000 -----25000$$$$$)

TRIM()::

The TRIM() function returns the vlues with out any space before and after the "String".

SELECT LENGTH(TRIM('arul   ')) from dual;
SELECT LENGTH(TRIM('arul')) from dual;
  Both return the same value---4

LTRIM()::

The LTRIM() mainly used for the TRIM the left values Which we pass in the condition(Like "0")

SELECT LTRIM('0000012334000','0') from dual
O/P Is------------1234000

RTRIM()::

The RTRIM() mainly used for the TRIM the Right values Which we pass in the condition(Like "0")

SELECT LTRIM('0000012334000','0') from dual
O/P Is------------000001234


REPLACE()::(String Based)

The REPLACE() function used to Replace the "String Based(Whic means continues character or Word)" values
which is mentioned in the query.

SELECT First_Name,REPLACE(First_Name,'ul','xy') from employees;
O/P---arul------arxy

TRANSLATE()::(Character Based)::

The TRANSLATE() function mainly used for Change the "Single Char"  which is Passed in the query.
SELECT TRANSLATE('WELCOME','E','L') FROM dual;
                                    ------
Translate the 'Char E' into "L".

O/P Is --WELCOME--------WLLCOML.

Read More