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.