Oracle PL/SQL - INSTR function

In this tutorial we will explain how to use the Oracle PL/SQL INSTR function with syntax and examples

INSTR Function : 

Syntax : INSTR ('input_string',  'search_string',  start_position,  occurrence);

• In Oracle PL/SQL INSTR function returns the position of the "search_string" in the given input_string.
• input_string  and search string can be of data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB
• return type is of data type NUMBER.
• The first position in the input_string is treated as 1.
• start_position is the position in the input_string from where the search will start.
• If start_position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.
• occurrence must be a positive integer.
• If search_string is not found in input_string, then the INSTR function will return 0.
• start_position and occurrence are optional and the default value is 1.

EXAMPLE 1 :

SELECT INSTR('DEVELOPERS ARENA','E',3, 2)
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  8

EXAMPLE 2 :

SELECT INSTR('DEVELOPERS ARENA','E',-2, 3)
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  4

EXAMPLE 3 :

SELECT INSTR('DEVELOPERS ARENA','E')
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  2

Note: In the above example 3,since start_position and occurrence are omitted, they are defaulted to 1. Hence the search will happen from the first position and the will return the first occurrence of the letter "E".

Oracle PL/SQL - ABS function

In this tutorial we will explain how to use the Oracle PL/SQL ABS function with syntax and examples

ABS Function : 

Syntax : ABS ('input_number');

• In Oracle PL/SQL ABS function returns the absolute value of the input number that is passed to the function.
• input_number is of numeric datatype.
• return type is same as the input datatype.

EXAMPLE 1 :

SELECT ABS(-143) "Absolute_value" FROM DUAL;

  Absolute_value
-----------------
             143

EXAMPLE 2 :

SELECT ABS(143.14) "Absolute_value" FROM DUAL;

  Absolute_value
-----------------
           143.14

Oracle PL/SQL - MONTHS_BETWEEN function

MONTHS_BETWEEN Function : 

Syntax : MONTHS_BETWEEN ('Date1','Date2');

• In Oracle PL/SQL MONTHS_BETWEEN function returns the number of months between two dates.
• Input argument to the MONTHS_BETWEEN function should be of data type 'date'.
• Return Type of MONTHS_BETWEEN is a number.
• If Date 1 is greater than Date 2 then the return number is positive
• If Date 1 is less than Date 2 then the return number is negative
• If Date 1 and Date 2  have the same day component or are the last day of the month, the return number is a whole number,Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components Date1 and Date2.

EXAMPLE 1 : ( Date 1 is greater than Date 2)

SELECT MONTHS_BETWEEN (TO_DATE('02-02-2015','MM-DD-YYYY'),
       TO_DATE('01-01-2015','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
----------
1.03225806

EXAMPLE 2 : ( Date 1 is less than Date 2)

SELECT MONTHS_BETWEEN (SYSDATE,SYSDATE+1) "Months"
    FROM DUAL;

Months
----------
-.03225806

EXAMPLE 3 : ( Date 1 and Date 2 has same day)

SELECT MONTHS_BETWEEN (TO_DATE ('2015-10-25', 'YYYY-MM-DD'), TO_DATE ('2015-03-25', 'YYYY-MM-DD')) "Months"
       FROM DUAL;

Months
----------
7

Oracle PL/SQL - LAST_DAY function

LAST_DAY Function : 

Syntax : LAST_DAY ('Date');

• In Oracle PL/SQL LAST_DAY function returns the last day of the input date argument.
• Input argument to the LAST_DAY function should be of data type 'date'.
• Return Type of LAST_DAY function is always date.

In the below Example 1 the LAST_DAY returns the last day for the month of March 2015.

In Example 2 , since it is a leap year the last day of the February month is 29th.

EXAMPLE :1 

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Month_End",
     FROM DUAL;
 
SYSDATE   MONTH_END 
--------- ---------
25-MAR-15 31-MAR-15 

EXAMPLE 2 :

SELECT LAST_DAY('20-FEB-2016') "MONTH_END"
FROM DUAL;

MONTH_END
---------------
29-FEB-16