 # Single Row Functions

Single row functions operate on a single row only then return one result for 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 the 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 an output, in this case, -1 is a length of before decimal point what we want to print.

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

``Ex1. SELECT TRUNC(456.5) FROM DUAL; ``

This query will return  456 as an 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 a return remainder of the division.

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

Mod always return that value that remains after divination this query will return 1.

### Date Function:

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

These functions come 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 the number of months between these two dates.

``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