Importance of SQL Character Functions in Oracle Database #DOTC_MDB

 Importance of SQL  Character Functions  in Oracle Database

 

Hello Everyone,

Today we will discuss about SQL functions in Oracle which plays important role in writing SQL queries. Before Starting to read this blog I suggest you to please go through my previous basic SQL blog for your reference.

We will discuss the below Functions in today’s blog.

1. ROUND

2. TRUNC

3. CEIL and FLOOR

4. SIGN AND ABS

5. NVL and NVL2

 

Let’s discuss Round Function in SQL: ROUND function is used to round a numeric value to a specified precision. If the value is less than 5, the function rounds down. If the value is greater than or equal to 5, the function rounds the value up. The function contains two parameters: The first is the value or column name, and the second is the precision. Round Function also used to suppress decimal values. ROUND Function enables you to round a numeric value that is returned in a result set.

Example of ROUND function to round one number up and another number down and also using ROUND function to round numeric values to whole numbers is as below. I use UNION Operator to see the output of both the query in one for better understanding.

SELECT ROUND(987654.1238, 2), ROUND(987654.8591, 2)

  FROM DUAL

UNION

SELECT ROUND(987654.1238),  ROUND(987654.8591)

  FROM DUAL ;

Output of above query is as below:

 

Another example of ROUND function to round numeric values to the left of the decimal point is as below.

select ROUND(987654.1238, -1), ROUND(987654.8591, -3)

  FROM DUAL;

Output of above query is as below

 

2. TRUNC : TRUNC function is an inbuilt function in PLSQL which is used to return a number truncated to a particular number of decimal places.

NOTE : Difference between ROUND and TRUNC is The ROUND function is used to round a numeric field to the nearest number of decimals specified whereas TRUNC Function returns a number truncated to a certain number of decimal places. So it always takes the lesser value.

Example : For Round 76 is nearer to 80 than 70 so ROUND  will return 80 whereas TRUNC function will return 70 even we use 74 also.

Let’s take an example of using TRUNC function to cut off digits to the right of the decimal point and to truncate numeric values to the whole numbers. I am using union operator to see the output of both.

SELECT TRUNC(987654.1238, 2), TRUNC(987654.8591, 2)

  FROM DUAL

UNION

SELECT TRUNC(987654.1238),  TRUNC(987654.8591)

  FROM DUAL ;

Output of above query is as below

 

Another Example of TRUNC function to truncate numeric values to the left of the decimal point, below is the query for that.

 

SELECT TRUNC(987654.1238, -1), TRUNC(987654.8591, -3)

  FROM DUAL;

Output of above query is as below.

 

If you see the output of both ROUND and TRUNC you will be able to understand the difference between both of them.

 

3. CEIL AND FLOOR:  CEIL function in Oracle returns the smallest integer value that is bigger than or equal to a number whereas the FLOOR function returns the largest integer value not greater than a number specified as an argument.

 

Below is the query for both CEIL and FLOOR . I used UNION operator to see the output of both function.

SELECT CEIL(987654.1238),  CEIL(987654.8591)

  FROM DUAL

UNION

SELECT FLOOR(987654.1238),  FLOOR(987654.8591)

  FROM DUAL ;

 

Output of above query is as below the second value in output is for FLOOR function.

 

Using ROUND, CEIL, TRUNC, and FLOOR functions in one query to see the output. Below is the Query.

SELECT ROUND(987654.8591), CEIL(987654.8591), TRUNC(987654.8591), FLOOR(987654.8591) FROM DUAL;

Output of above query is as below.

 

4. SIGN and ABS Function: ABS function in SQL returns the absolute value of a specified value. You provide the value as an argument. The return value is of the same type as the argument whereas SIGN Function returns the sign of a number , if number is 0 it will return 0 if number is less than 0 it returns  -1 and if number is greater than 0 it returns 1.

Below is the query to understand ABS function. I used UNION ALL operator to see the output of both positive and negative number as ABS function in SQL returns the absolute value of a specified value.

SELECT ABS(567) FROM DUAL

UNION ALL

SELECT ABS(-567) FROM DUAL;

Output of above query is as below.

 

Below is the example of SIGN with positive, negative and zero value.

SELECT SIGN(567),SIGN(-567),SIGN(0) FROM DUAL;

Output of above query is as below.

 

Using both SIGN and ABS in one query to see the data.

SELECT SIGN(ABS(567) *25 +25)

FROM DUAL

UNION ALL

SELECT SIGN(ABS(-567) *25 +25)

FROM DUAL

UNION ALL

SELECT SIGN(ABS(0) *25 +25)

FROM DUAL;

Output of above query is as below:

 

5. NVL and NVL2 : Oracle NVL function allows you to suppress NULL values with any value passed by expression 1 whereas The NVL2 function examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned.

Syntax of NVL :

NVL (EXPR1, EXPR2)

EXPR1 : Expression that may contain a null whereas EXPR2 suppress the NULL values with your argument.

Syntax of NVL2 :

NVL2 (EXPR1, EXPR2, EXPR3)

Taking one example from employee table where commission field is null only value for commission is 2 records i.e. 12 . Below is the query

SELECT  LOCATION,SALARY, NVL(COMMISION, 0),

    (SALARY*2) + (SALARY*2*NVL(COMMISION, 0))

      COMMISION_VALUE FROM EMPLOYEE;

Output of above query is as below. Note :Data will not updated in base table.

 

Example of NVL2 function is as below , in the below query if first expression is not null then it will give the value as salary + commission and for null value it will return Salary .

SELECT LOCATION, SALARY, COMMISION,

 NVL2(COMMISION, 'SALARY+COMMISION', 'SALARY')

 TOTAL_VALUE FROM EMPLOYEE;

Output of above query is as below:

 

Please read this blog carefully and let me know if you have any question as this functions are very important and vastly used in SQL and PL/SQL block . In this blog we discussed Oracle number and miscellaneous substitution functions. In next blog I will come up with more SQL functions.

Thanks.