Oracle PL/SQL - INSTRB function

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

INSTRB Function : 

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

• In Oracle PL/SQL INSTRB function returns the position of the "search_string" in the given input_string using bytes instead of characters.
• 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 INSTRB function will return 0.
• start_position and occurrence are optional and the default value is 1.

EXAMPLE 1 :

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

EXAMPLE 2 :

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

Oracle PL/SQL - SIGN function

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

SIGN Function : 

Syntax : SIGN(input_number) ;

• In Oracle PL/SQL , SIGN is a inbuilt function which is used to determine the sign of a give number.
• Returns -1 if input_number is < 0
• Returns 1 if input_number is > 0
• Returns 0 if input_number is = 0
• Return type is number and is either -1 ,1 or 0.

EXAMPLE :1 (When Input number is < 0 )

SELECT SIGN(-100) FROM DUAL;

SIGN
--------------------
-1

EXAMPLE :2 ( When Input number is > 0)

SELECT SIGN(143.12) FROM DUAL;

SIGN
--------------------
1

EXAMPLE :3 (When Input number is = 0 )

SELECT SIGN(0) FROM DUAL;

SIGN
--------------------
0

Oracle PL/SQL - CURRENT_DATE function

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

CURRENT_DATE Function : 

Syntax : CURRENT_DATE ;

• CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
• Return type is of data type DATE.
• There are no input parameters to this function.
• The output date can be altered by changing the timezone.

In the below Example 1 the CURRENT_DATE returns the date as per the timezone of UTC-5 and in NLS_DATE_FORMAT of DD-MON-YYYY HH24:MI:SS

In Example 2 , the session is altered to a timezone of UTC-7.

EXAMPLE :1 

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT CURRENT_DATE FROM DUAL;

CURRENT_DATE
--------------------
31-MAR-2015 18:14:08

EXAMPLE :2

ALTER SESSION SET TIME_ZONE = '-7:0';

SELECT CURRENT_DATE FROM DUAL;

CURRENT_DATE
--------------------
31-MAR-2015 16:14:08

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

Oracle PL/SQL RR Datetime Format

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year

If the two digits of the current year are :		If the Specified two digit year is
		00-49				50-99
		00-49	The return date is in the current century			The return date is in the century before the current one
		50-99	The return date is in the century after the current one			The return date is in the current century

• If the specified two-digit year is 00 to 49, then
  • If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  • If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
• If the specified two-digit year is 50 to 99, then
• If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
• If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year

Assume these queries are issued between 1950 and 1999

Example 1:

SELECT TO_CHAR(TO_DATE('09-MAR-95', 'DD-MON-RR') ,'YYYY') "Year"  FROM DUAL;

YEAR
-----------
1995

Example 2:

SELECT TO_CHAR(TO_DATE('09-MAR-15', 'DD-MON-RR') ,'YYYY') "Year"   FROM DUAL; 

YEAR
-----------
2015

Assume the above queries are run between 2000 and 2049

Example 1:

SELECT TO_CHAR(TO_DATE('09-MAR-95', 'DD-MON-RR') ,'YYYY') "Year"  FROM DUAL;

YEAR
-----------
1995

Example 2:

SELECT TO_CHAR(TO_DATE('09-MAR-15', 'DD-MON-RR') ,'YYYY') "Year"   FROM DUAL; 

YEAR
-----------
2015

Oracle PL/SQL - TO_CHAR function

TO_CHAR Function : 

Syntax : TO_CHAR (input_value, fmt_mask, nls_language); 

• input_value can be either a string,number or a interval of datetime.
• Return type is VARCHAR2
• fmt_mask is the format type to which the input value is converted. This parameter is optional.
• nls_language determines the language in which the month,day and other abbreviated strings are returned. This parameter is optional.

Example 1:

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') FROM DUAL;

RESULT
-----------
2015/03/09

Example 2:

SELECT TO_CHAR(SYSADTE, 'Month DD, YYYY') FROM DUAL;

RESULT
-----------
March 09, 2015

Parameter
Explanation
YYYY
 4-digit year
YEAR
Year, spelled out
YYY
YY
Y
Last 3, 2, or 1 digit(s) of year.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
4-digit year based on the ISO standard
Q
Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM
Month (01-12; JAN = 01).
MON
Abbreviated name of month.
MONTH
Name of month, padded with blanks to length of 9 characters.
RM
Roman numeral month (I-XII; JAN = I).
WW
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh

day of the year.
W
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW
Week of year (1-52 or 1-53) based on the ISO standard.
D
Day of week (1-7).
DAY
Name of day.
DD
Day of month (1-31).
DDD
Day of year (1-366).
DY
Abbreviated name of day.
J
Julian day; the number of days since January 1, 4712 BC. specified with J must be integers
HH
Hour of day (1-12).
HH12
Hour of day (1-12).
HH24
Hour of day (0-23).
MI
Minute (0-59).
SS
Second (0-59).
FF 
Fractional seconds.
 SSSSS
Seconds past midnight (0-86399)

Oracle PL/SQL - ADD_MONTHS function

ADD_MONTHS Function : 

Syntax : ADD_MONTHS (date,n); 

• Return type of ADD_MONTHS is always a date.
• n is a integer or any other value that can be converted to a integer
• date can be either in a date format or any other value that can be converted to a date format.

Example 1:

SELECT ADD_MONTHS('02-MAR-2015',3) "RESULT" FROM DUAL

RESULT
-----------
02-JUN-2015

Example 2:

SELECT ADD_MONTHS('30-JAN-2015',1) "RESULT" FROM DUAL

RESULT
-----------
28-FEB-2015

Note : If the date is last day of the month or the resulting month has less number of days than the input date, then the resulting date is always the last day of the month

Oracle PL/SQL - CHR Function

CHR Function : 

Syntax : CHR (number);

               CHR (number USING NCHAR_CS)

• 
  
• In Oracle PL/SQL CHR function returns a ASCII equivalent of the input numeric value that is passed to the function.
• The Function will return a national character if you specify USING NCHAR_CS

Example 1: 

SELECT CHR(72)||CHR(69)||CHR(76)||CHR(76)||CHR(79)"RESULT" FROM DUAL;

RESULT
-------
HELLO 

Example 2 :

SELECT CHR (50052 USING NCHAR_CS) "RESULT" FROM DUAL; 

RESULT 
-- ---
Ä 

Oracle PL/SQL - ASCIISTR Function

ASCIISTR Function : 

Syntax : ASCIISTR ('input_string')

• In Oracle PL/SQL ASCIISTR function takes a string (or an expression that resolves to a string) and returns an ASCII version of the string in the current database character set.
• Non ASCII Characters are converted to the form \xxxx (in the below example \00C4) where \xxxx represents the UTF-16 code.
• ASCIISTR is useful in converting the Unicode characters to a ASCII set value.

Example: 

SELECT ASCIISTR('XYZÄABC') FROM DUAL;

ASCIISTR
----------
XYZ\00C4ABC

Oracle PL/SQL String / Character Functions - ASCII Function

ASCII Function : 

Syntax : ASCII ('input_character')

• In Oracle PL/SQL ASCII function returns the numeric value associated with a character.
• Return Type of ASCII function is NUMBER.
• This Function doesn't support CLOB datatype as input
• Input_character should be of one character. If the input character has more than one letter, than the function takes the first character of the string.

In the below example the first result returns  the numeric equivalent of the character 'a'. In the second example, since more that one character is entered it has returned the ASCII value of the first character which is 'd'.

Example: 

ASCII('a')
Result: 97

ASCII('developersarena')
Result: 100

Oracle PL/SQL FIRST and LAST Function

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. The FIRST and LAST functions eliminate the need for self joins or views and enable better performance.

Syntax aggregate_function KEEP (DENSE_RANK FIRST ORDER BY Order_by_clause) [OVER (query_partitioning_clause)] aggregate_function KEEP (DENSE_RANK LAST ORDER BY Order_by_clause) [OVER (query_partitioning_clause)]

Example :

SELECT student_no,

       deptno,

       total_marks,

       MIN(total_marks) KEEP (DENSE_RANK FIRST ORDER BY total_marks) OVER (PARTITION BY deptno) "Lowest_Mark",

       MAX(total_marks) KEEP (DENSE_RANK LAST ORDER BY total_marks) OVER (PARTITION BY deptno) "Highest_Mark"

FROM   students_table

ORDER BY deptno, total_marks;



STUDENT_NO   DEPTNO  TOTAL_MARKS     LOWEST_MARK    HIGHEST_MARK

---------- ----------    ----------    --------- ----------

      1001         100       1400       1350       1600

      1008         100       1350       1350       1600

      1009         100       1600       1350       1600

      1005         200        850        850       3000

      1006         200       1100        850       3500

      1002         200       2975        850       3500

      1003         200       3500        850       3500

      1004         200       3000        850       3500

      1007         200        950        850       3500



      

Difference Between Oracle RANK() and DENSE_RANK()

RANK

Let's assume we want to assign a rank to students in a college department wise based on total_marks. Below is the example with the results
when a RANK() function is used.

SELECT studentno,

       deptno,

       total_marks,

       RANK() OVER (PARTITION BY deptno ORDER BY total_marks) "rank"

FROM   students_table;



     STUDENT_NO     DEPTNO        total_marks       rank
    ----------      --------       ----------       -------
 
      1001         100               1300          1

      1005         100               1300          1

      1006         100               1400          3

      1009         200               1100          1

      1002         200               1150          2

      1004         200               1150          2

      1003         200               1200          4

  From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is not consecutive. Now lets see how using a DENSE_RANK() will change these results.

DENSE_RANK

SELECT studentno,

       deptno,

       total_marks,

       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY total_marks) "rank"

FROM   student_table;



     STUDENT_NO     DEPTNO        total_marks       rank

    ---------- ----------         ---------- ----------

      1001         100               1300          1

      1005         100               1300          1

      1006         100               1400          2

      1009         200               1100          1

      1002         200               1150          2

      1004         200               1150          2

      1003         200               1200          3

From the above example when two students have the same total_marks they are assigned the same rank and the next student rank after this is consecutive.

How to use Oracle PL/SQL Rank function

What is Oracle Rank Function ?

Oracle Rank() function

In Oracle PL/SQL, RANK function is a built in analytic function which is used to rank a record within a group of row.

 The rank function can cause non-consecutive rankings if the tested values are the same. If you need to have the consecutive rankings you need to use dense_rank() function.

Example Syntax:

Used as an Aggregate function:

RANK (expression) WITHIN GROUP (ORDER_BY expression [ASC | DESC] NULLS [FIRST | LAST]  )

Used as an Analytic function:

RANK () OVER (PARTITION BY expression ORDER BY expression)

Example Usage:

The below SQL query uses demonstrates analytic behavior of RANK function. It ranks the salary of the employees working in the same department.

SELECT DEPT, STUDENT_ID, RANK() OVER 

(PARTITION BY DEPT ORDER BY TOTAL_MARKS) RANK

FROM STUDENTS_TABLE

/



      DEPT      STUDENT_ID   RANK

----------      ----------   ---------

        100      100001        1

        100      100009        2

        101      100005        1

        101      100004        2

        101      100002        3

        102      100006        1

        102      100007        2

       

7 rows selected.