Functions

Functions:

            A function is a reusable program which always  returns a value. It accepts argument and returns a value.

It’s also does some of the following

-          Perform calculations on the data

-          Modify the individual Items

-          Manipulate Output for  groups of rows

-          Formats dates and Numbers

-          Convert column data types.

Single Row Functions
Ø  Manipulate Data Items
Ø  Accepts arguments and Return a Value
Ø  Act on each row returned
Ø  Return One result per row
Ø  May Modify the DataType
Ø  Can be Nested
Ø  Accepts arguments which can be a column or an Expression

Multiple Row Functions

Multiple row functions acts on group of rows and returns single record per group. These functions are called Group Functions.


Types Of Single Row Functions:

Character Functions           : Manipulate strings of characters.

Numeric Functions              : Perform calculations.

Date Functions                     : Process dates and times.

Conversion Functions         :  Convert a value from one database type to another.

Null Functions                      : Handle Null Values

Analytical Functions           : To Analyze data.

CHARACTER FUNCTION:

UPPER: Converts to Upper case.

SELECT UPPER(ENAME) FROM EMP;

SELECT UPPER('description') FROM DUAL;

LOWER: Converts to Lower Case.

Ø  SELECT LOWER(ENAME), LOWER(JOB) FROM EMP;

Ø  SELECT  LOWER('DESCRIPTION') FROM DUAL;

INITCAP: Converts first letter to Upper case and rest to lower case.

Ø  SELECT INITCAP(ENAME) FROM EMP;

Ø  SELECT INITCAP('ORACLE') FROM DUAL;

Ø  SELECT INITCAP('oracle') FROM DUAL;

LENGTH: It returns the length of the given string.

Ø  SELECT ENAME, LENGTH(ENAME) FROM EMP;

Ø  SELECT LENGTH('ORACLE'), LENGTH(' ORACLE') , LENGTH('ORA_SQL'), LENGTH('ORA CLE') FROM EMP;

Q) LIST THE EMPLOYEES HAVE FIVE CHARACTERS

    SELECT * FROM EMP WHERE LENGTH(ENAME)=5;

Q) LIST THE EMPLOYEES HAVING 5 CHARACTERS IN NAME AND JOB

    SELECT * FROM EMP WHERE LENGTH(ENAME)=5 AND LENGTH(ENAME)=5;

REVERSE: IT returns the string in reverse order.

SELECT ENAME, REVERSE(ENAME) FROM EMP;

SELECT 'ORACLE' AS NAME ,REVERSE('ORACLE') AS REVERSE FROM DUAL;

REPLACE: It replaces old value to a new value.

Syntax:  REPLACE('COLUMN NAME', 'SEARCH VALUE WITH' ,'VALUE REPLACED')

Ø  SELECT REPLACE('ORACLE','L','T') FROM DUAL;

Ø  SELECT REPLACE('ORACLE','L',NULL) FROM DUAL;

Ø  SELECT LENGTH(REPLACE(' OR A ',' ',NULL)) FROM DUAL;

Q) HOW TO FIND THE CHARACTER HAS BEEN REPLACED.

    SELECT LENGTH('ORACLE SQL'), LENGTH(REPLACE('ORACLE SQL','L',NULL)) FROM DUAL;

Q) WRITE A QUERY TO GET THE NUMBER OF OCCURANCES OF 'A' IN NAME OF EMPLOYEE TABLE

     SELECT ENAME, LENGTH(ENAME), LENGTH(REPLACE(ENAME,'A',NULL)), LENGTH(ENAME)-LENGTH    

     (REPLACE(ENAME,'A',NULL)) AS REP_NM FROM EMP;

Scenario:

1) Can replace single string with many value..

2) Many Characters to single value

3) Can replace any letter with null, since it will not occupy any space.

TRIM: It trim the blank spaces from both sides.

LTRIM: It trim the blank spaces from Left Hand Side.

RTRIM: It trim the blank spaces from Right Hand Side.

Ex:

SELECT TRIM(' ORACLE '), LENGTH(TRIM(' ORACLE ')) FROM DUAL;

SELECT LTRIM(' ORA '), LENGTH(LTRIM(' ORA ')) FROM DUAL;

SELECT RTRIM(' ORA '), LENGTH(RTRIM(' ORA ')) FROM DUAL;

CONCAT: Its joins the table columns or values together.

SELECT CONCAT(ENAME,JOB), ENAME||' '||' IS A EMPLOYEE.' AS “DESCRIPTION” FROM EMP;

LPAD: Pads the character value to Right Justified.

SELECT ENAME,LPAD(ENAME,10,'$') FROM EMP;

RPAD: Pads the character values to Left Justified.

SELECT ENAME,RPAD(ENAME,10,'$') FROM EMP;

SUBSTR:

It replaces or extracts ‘n’ char frothe m a given position of the string.

Ex: SELECT ENAME, SUBSTR(ENAME,1,3), SUBSTR(ENAME,2,4) FROM EMP;

NOTE:

·         If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in string).

·         If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.

·         If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.

·         If length is a negative number, then the SUBSTR function will return a NULL value.

SELECT ENAME, SUBSTR(ENAME,1,3) "1-3", 
            SUBSTR(ENAME,2,4) "2-4", 
            SUBSTR(ENAME,3) "3-N" 
FROM EMP;

Syntax: SUBSTR( string, start_position, [ length ] )

SUBSTR('ORACLE',1,3)  => ORA

SUBSTR('ORACLE',2,4)  => RAC

SUBSTR('ORACLE',4)     => CLE

SUBSTR('ORACLE',-3,2) => CL

SUBSTR('ORACLE',-2)    => LE

Q) Display first 3 and last 3 characters of employee name and seperated by '_'

     SELECT SUBSTR(ENAME,1,3)||'_'||SUBSTR(ENAME,-3) "NAME" FROM EMP;

  SELECT SUBSTR('ABC_blahblahblah', 0, INSTR('ABC_blahblahblah', '_')-1) AS output 

  FROM DUAL;    / Below explained /

  SELECT SUBSTR(t.column, 0, INSTR(t.column, '_')-1) AS output

  FROM YOUR_TABLE t

INSTR: Returns the position of the character or a string in a given string.

SYN: INSTR( STRING, SUBSTRING [, START_POSITION [,  NTH_APPEARANCE ] ] )

SELECT  ENAME, INSTR(ENAME,'A',1,1) AS “FIRST”, INSTR(ENAME,'A',1,2) AS “SECOND” FROM EMP;

SELECT INSTR('DESCRIPTION','T') FROM DUAL;   => 8

SELECT INSTR('DESCRIPTION','T',1,1) FROM DUAL; => 8

SELECT INSTR('DESCRIPTION','T',1,2) FROM DUAL; => 0

SELECT INSTR('DESCRIPTION','I',1,2) FROM DUAL; => 9

SELECT INSTR('DESCRIPTION','I',2,2) FROM DUAL; => 9

INSTR('Tech on the net', 'e')                  would return 2; the first occurrence of 'e'

INSTR('Tech on the net', 'e', 1, 1)           would return 2; the first occurrence of 'e'

INSTR('Tech on the net', 'e', 1, 2)           would return 11; the second occurrence of 'e'

INSTR('Tech on the net', 'e', 1, 3)           would return 14; the third occurrence of 'e'

INSTR('Tech on the net', 'e', -3, 2)          would return 2

Q) Query to display jobs of employee has letter M

    SELECT * FROM EMP WHERE INSTR(JOB,'M',1,1,)>0;

Q) Query to display the employees whose name doesnot have letter s

    SELECT * FROM EMP WHERE INSTR(ENAME,'S',1,1)=0;

Q) Query to display who have 2L in their names

   SELECT * FROM EMP WHERE INSTR(ENAME,'L',1,1)>1;

Q) Query to retrive mail id's omitt domain name after @ Symbol.

    SELECT SUBSTR(EMAIL,1,INSTR(EMAIL,'@',1,1)-1) "EMAIL",EMAIL FROM HR_EMP;

    SELECT SUBSTR('VIV@GMAIL.COM',1,INSTR('VIV@GMAIL.COM','@',1,1)-1) FROM DUAL;

Note:

-          If start position is not given by default it takes one. (first occurrence)

Ex: SELECT ENAME,INSTR(ENAME,'A') FROM EMP;

-        Ø  If nth occurrences are not given by default it takes as 1^st Occurrence.

     Ø If substring is not found in string, the INSTR returns zero.

     Ø If start position is returned as zero then zero will be returned.

     Ø  If start position is +ve then INSTR starts from the beginning

Ex; instr(‘Oracle’,’a’,1,3)

-          If start position is –ve then Instr starts from right to left (end to beginning) but counts

from left to right.

Ex; select instr(‘oracle’,’l’,-1) from dual;

-          If given more than one character to be searched in the string, then it gives the position of the 1^st char only.

Numeric Functions:

ABS(-n)                              - Absolute value

SQRT(n)                             - square root of the given number

POWER(X,Y)                       - returns x power y value

MOD(X,Y)                           - returns the remainder when x is divided by y

exp(x)                                - returns the e power x value (e = 2.71)

select abs(-5), abs(10), sqrt(100), power(2,4), mod(20,6), 20/6 as qu, exp(2)

from dual;

SIGN(n)

========

It returns +1 for +ve value, -1 for -ve value, zero for zero.

select sign(10), sign(-5) , sign(0) from dual ;

..

..

begin

  if sign(veno) = -1 then

                print ('-ve number');

  end if;

end;

/

CEIL & FLOOR

==========

-ceil  returns highest integer

-floor returns lowest  integer

 select ceil(96.1), floor(96.99) from dual;

 select ceil(-96.1), floor(-96.99) from dual;

GREATEST & LEAST

================

-returns the highest among  list of values/columns

-returns the least   among  list of values/columns

select greatest(10,40,20) from dual;

select least(10,40,20) from dual;

select greatest(sal, comm, bonus) from emptab;

select max(sal) from dual;

-max returns highest among the rows whereas greatest returns highest among  list of columns

-max can take 1 parameter whereas greatest can take any parameters.

ROUND & TRUNC

=============

- The ROUND function rounds the column, expression, or value to n decimal places.

- If the second argument is 0 or is missing, the value is rounded to zero decimal places.

- If the second argument is 2, the value is rounded to two decimal places. Conversely,

      if the second argument is -2, the value is rounded to two decimal places to the left.

- The ROUND function can also be used with date functions.

Select Round(56.786,2) From DUAL;        --> 56.79

Select Round(56.784,2) From DUAL;        --> 56.78

Select Round(45.923,2), Round(45.923,0), Round(45.923,-1) From DUAL;

ROUND(SYSDATE,'MONTH')                        01-AUG-95

ROUND(SYSDATE ,'YEAR')                          01-JAN-96

TRUNC(SYSDATE ,'MONTH')                        01-JUL-95

TRUNC(SYSDATE ,'YEAR')                           01-JAN-95

The TRUNC function truncates the column, expression, or value to n decimal places.

Syntax:

Trunc(Number, Decimal_Places);

Trunc(Date , Format );

SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;

select trunc(56.789,2) from dual;              --> 56.78

TRUNC(TO_DATE('22-AUG-03'), 'YEAR')

TRUNC(TO_DATE('22-AUG-03'), 'Q')

TRUNC(TO_DATE('22-AUG-03'), 'MONTH')

Date Functions:

  

SYSDATE      [dd-mm-yyyy hh:mi:ss]  - date + time

SYSTIMESTAMP [dd-mm-yyyy hh:mi:ss:fffffffff] + TZ   -  date + time + fractional secs + TZ 

The default display and input format for any date is DD-MON-RR. 
Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999 A.D.

alter session set nls_date_format = 'mm/dd/yyyy hh:mi:ss'

SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;

=====================================================


LAST_DAY : Returns the date of the last day of month with respect to given date.

select last_day(sysdate) from dual ;

select ename, hiredate, last_day(hiredate) from emp ;

Display First salaried date of Employee
- select ename, hiredate, last_day(hiredate)+ 1 "Salaried_Day" from emp ;

=================================


NEXT_DAY : Returns the date of next day with respect to given date & day

select next_day(sysdate, 'friday') from dual;

 Select next_day(sysdate,'MONDAY' ) + 7 from dual;

select next_day(sysdate, 'thu') from dual;

select next_day(sysdate, 'thu') + 7 from dual;


Adding days to a date
     ==============

select sysdate+2 from dual;

select sysdate-2 from dual;


=============================================

ADD_MONTHS : Adds/subtracts given months to a date

select add_months(sysdate,5) from dual ;

select add_months(sysdate,24) from dual ;

select add_months(sysdate,-10) from dual ;

select add_months(sysdate,-20) from dual ;

select ename, hiredate, add_months(hiredate,6) as hk_dt from emp;

=======================================================


MONTHS_BETWEEN : Difference between any 2 dates in months.

select ename, months_between(sysdate,hiredate) from emp;

select months_between('06-JAN-2019', sysdate) from dual;

select ename, months_between(sysdate,hiredate)/12 as years from emp;

select ename,hiredate, round(months_between(sysdate,hiredate)/12,0) from emp;

select ename, trunc(months_between(sysdate,hiredate)) from emp;

select ename,trunc(months_between(sysdate,hiredate)/12) as exp_yrs from emp;

==========================================================

NEW_TIME : IT CONVERTS FROM 1 TIMEZONE TO ANOTHER TIMEZONE

CURRENT_DATE : 
IT DISPLAYS CLIENT'S date / session's date whereas SYSDATE will display 
server's date.

DBTIMEZONE : IT RETURNS TIMEZONE

===========================================================
    
   Date Operations:
You can perform the following operations:

Arithmetic with Dates
==================
• Add or subtract a number to or from a date for a resultant date value.

• Subtract two dates to find the number of days between those dates.

• Add hours to a date by dividing the number of hours by 24.

Operation             Result                   Description

date + number         Date                     Adds a number of days to a date
date - number          Date                     Subtracts a number of days from a date
date - date               Number of days      Subtracts one date from another
date + number/24     Date                     Adds a number of hours to a date

ROUND(date[,'fmt']) : 
      Returns date rounded to the unit specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

TRUNC(date[, 'fmt']) : Returns date with the time portion of the day truncated to the unit specified by the format model fmt. 

If the format model fmt is omitted, date is truncated to the nearest day.

  Other Date Functions are:

   CURRENT_DATE, CURRENT_TIMESTAMP, EXTRACT (datetime), FROM_TZ, LOCALTIMESTAMP, NEW_TIME, NUMTODSINTERVAL, NUMTOYMINTERVAL, ROUND(date),
SESSIONTIMEZONE, SYS_EXTRACT_UTC, TO_CHAR(datetime), TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_DSINTERVAL, TO_YMINTERVAL, TRUNC (date), TZ_OFFSET
   
    ===========================================================


NULL Functions:
==========
Nvl
Nvl2
Nullif
Coalesce

===========================================================

NVL: substitute only for NULL value

select ename, nvl(comm,-100) from emp;

select ename, sal, comm, sal+nvl(comm,0) as tsal , Sal + Comm NN from emp;

select ename, nvl(to_char(comm),'no comm') from emp;

============================================================

NVL2: substitute  for both NULL & not null
Syntax: 

NVL2(Column_Name , 'NOT NULL Value', 'Null value' ) 

select ename, nvl2(comm,'yes','no') from emp;

Select Empno, Ename, Job, Nvl2(comm,'Comm','No Comm') As Comm From Emp;


Difference between NVL & NVL2
=====================
NVL                                                               NVL2
==== ====
Provides substitute value for NULL Provides substitute value for NULL & NOT NULL
        

============================================================

NULLIF:  compares 2 values [columns], returns NULL if they are same, else returns 1st value.
     ======

select nullif(10,20), NULLIF(20,20) from dual;

 select nullif(10,20), NULLIF(20,20), Nvl2(NULLIF(20,20)  ,'NOT SAME', 'SAME') from dual

select ename, sal, comm, hiredate, nullif(sal,comm) from emp;


Assignment - Display ename and their status. 
[Status is "Same" if their sal=comm else it is "Different"]

select ename, nvl2(nullif(sal,comm),'diff','same') as status from emp;


Assignment - display ename & dname. If deptname dont have an emp, then display him as 'New Dept'.

select nvl(A.ename,'NewDept'), B.dname
from emp A left join dept B on (A.deptno=B.deptno);

select nvl(A.ename,'NewDept'), B.dname
from emp A Right  join dept B on (A.deptno=B.deptno);

Select nvl(A.ename,'NewDept'), B.dname
from emp A left join dept B on (A.deptno=B.deptno);
Union
Select nvl(A.ename,'NewDept'), B.dname
from emp A Right  join dept B on (A.deptno=B.deptno);
=======================================================

COALESCE
     ========

Returns the first NOT NULL value.

coalesce(col1, col2, col3, ....)


if col1 is not null then
display col1.

if col1 is null AND col2 is NOT NULL then
display col2.

if col1 is null AND col2 NULL AND col3 is not null then
display col3.

if all are NULL it returns NULL itself.


 select comm, mgr, empno, coalesce(comm, mgr, empno) as val
    from emp;

=========================================================