SQL Functions Part 1 (Number Functions).

We have many built in functions available in SQL which are very useful while working with the SELECT statements. Depending on how they are working and how they are producing the results, SQL functions are divided into two types.

    1. Single row Functions
    2. Multiple row Functions.

In this post I want to discuss about some of the single row functions available in SQL.

Single row Functions ::


     The functions which will work on each row of a table and which will produce a result per each row are called as Single row functions. These functions will produce a result for each row they are effecting.

We have six types of Single row Functions available in SQL.

   1. Number Functions
   2. Character Functions
   3. Date Functions
   4. Conversion Functions
   5. General Functions
   6. Conditional Functions

In this post I want to discuss about Number functions. I will discuss about remaining functions in my coming posts.

1. Number Functions ::


  The functions which will work on numeric data are called as Number Functions. These functions will accept numeric input and will produce the numeric output.

 There are three numeric functions available in SQL.

 a) Round()
 b) Trunc()
 c) Mod()

Round() Function :


   This function is used to round the column or value or result of an expression to specified decimal places.
This will follow the mathematical rounding of operation rules.

  Ex : if we are rounding 423.456734 to 2 decimal places then result will be 423.46. (In rounding of operation 1 will be added to preceding number if current number is >=5).

 Syntax :: Round(input number, precision)

  Here precision will decide the number of decimal places to be present in the output.

 Ex : SELECT Round(2345.76234,2) FROM dual;

  This query will give result as 2345.76.

 If we exclude the precision in the syntax, then by default database will take it as 0.

 Ex : SELECT Round(2345.76234) FROM dual;

 This query will give result as 2346.

 If we give precision as negative value then the numbers to the left of decimal point will be rounded.

  Ex : SELECT Round(2345.7634,-2) FROM dual;

  This query will give result as 2300. Let me explain this. If, we gave negative precision then the database will replace those many digits to the left of decimal point with '0' and will round that number to nearest whole number. In this example we gave precision as -2. So, it will replace 4,5 with two '0' s. After replacing this, preceding number will be decided by nearest whole number concept. Nearest whole number to 345 is 300.
So, we will get result as 2300.

   SELECT Round(2366.7634,-2) FROM dual;

 This query will give result as  2400 as nearest whole number to 366 after replacing two '0' s is 400.

This is how we will use Round() function.

Trunc() Function ::


  This function is used to truncate the value or column or result of an expression to specified decimal places. This function is similar to Round() function but the only difference is here rounding of rules in mathematics are not applicable.

 Ex : if we are truncating 423.456734 to 2 decimal places then result will be 423.45.

 syntax :: Trunc(input number, precision).

   Here also the precision will decide the number of decimal places to be present in the output.

Ex : SELECT Trunc(4235.67895,2) From dual;

  This will give us result as 4235.67. If we do rounding of on this we will get, 4235.68 due to rounding of rules. But truncate will just truncate the number to specified decimal places.

  If we exclude the precision in the syntax then by default database will take it as zero.

 Ex : SELECT Trunc(4235.67895) FROM dual;

  This will give us result as 4235.

 If we give precision as negative value then the numbers to the left of decimal point will be truncated.

 Ex : SELECT Trunc(4235.67985) FROM dual;

  This will give us result as 4200. Here also nearest whole number concept will not be followed. This will just replace those many number of digits specified in negative precision with '0' s.

 Mod() Function ::


    This function will be used to find the remainder of a division of a number with another number. This will contain two input numeric arguments.

 Syntax ::  Mod(number1,number2).

    This will give us the remainder of division number1/number2.

  Ex : Select Mod(17,5) From Dual;

    This will give us the result as 2.

 Division operator which we have discussed in previous posts will give us quotient of the division. If we want to find out the remainder of the division then we have to use the Mod function.

 These are the different numeric functions which are available in SQL.