Single Row Functions

Single row functions operate on a single row only then return one result each row, there are many types of single-row functions.

Following are the types of Single Row Functions.

  1. Number Function.
  2. Date Function.
  3. Character Function.
  4. Conversion Function.
  5. General Function.

Number Function

Always accept only numeric values and also return always numeric values. Number Function has three sub-functions like ROUND, TRUNC, MODE.

ROUND:- It’ll be round a value to specified decimal point.

Ex1:- SELECT ROUND(456.5) FROM DUAL; 

This query will return 457 as an output because of the round function round that value which comes after the decimal point and 1 add-in before the decimal point value.

Ex2: SELECT ROUND(456.526,2) FROM DUAL; 

This query will return 456.53 as an output, in this case, 2 is the length of the decimal point that we want to print with value.

Ex3: SELECT ROUND(456.526,-1) FROM DUAL; 

This query will return 460 as a output, in this case -1 is a length of before decimal point what we want to print.

TRUNC:- It’ll be truncates a values specified decimal point.

Ex1. SELECT TRUNC(456.5) FROM DUAL; 

This query will return  456 as a output, trunc never be increment in value.

Ex2. SELECT TRUNC(456.426,2) FROM DUAL; 

This query will return 456.42 as output, 2 is a length of after decimal point value.

Ex3. SELECT TRUNC(456.426,-1) FROM DUAL; 

This query will return 450 as output, -1 is a length of before decimal point value.

MOD:- It’ll be always return reminder of division.

Ex1: SELECT MOD(100,3) FROM DUAL; 

Mod always return that value which is remain after divination this query will return 1.

Date Function: 

Always work on DATE type Data type except MOTHS_BETWEEN function because it return a number.

These functions comes under in Date Function.

  • MONTHS_BETWEEN (Count number of months between two dates).
Ex1: SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) RESULT FROM OCP; 

This will show you the number of months between in sysdate and hiredate.

Ex2. SELECT MONTHS_BETWEEN('06-SEP-2014','06-JAN-1989') FROM DUAL; 

This will show you number of months between these two dates.

  • ADD_MONTHS (Add number of months in date).
Ex: SELECT ADD_MONTHS(SYSDATE,6) FROM DUAL; 

This will show you a date which is after six months.

  • NEXT_DAY (It’ll show next day of calendar date specified).
Ex1: SELECT NEXT_DAY(SYSDATE,'SUNDAY') FROM DUAL;
Ex2: SELECT NEXT_DAY('06-SEP-2014','MONDAY') FROM DUAL;
  • LAST_DAY (It’ll show the last day of the month).
Ex1: SELECT LAST_DAY(SYSDATE) FROM DUAL;
Ex2: SELECT LAST_DAY('06-JAN-1989') FROM DUAL;
  1. Conversion Function: It converts a value from one data type to another data type.
  2. General Function: It has many types of sub-functions like NULLIF, NVL, NVL2, COALESCE, CASE, DECODE, these all functions have different functionality.

Read – How to create a SEQUENCE step by step

subscribe on youtube

1 thought on “Single Row Functions”

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks