# Useful Examples For DBAs of Single Row Numeric Functions

In the previous post, we started talking about **Single Row Functions**, which return a single value (row) for each row of a queried object, basically they operate on one row at a time. Today we will talk about **Numeric Functions**, and see some examples! Single Row Numeric Functions can be found in the SELECT, WHERE, START WITH and CONNECT BY, HAVING clauses of a select statement.

Numeric Functions accept an input and return a value. Both input and return values are numeric. The return value is usually a NUMBER that can be accurate up to 30-36-38 decimal digits, depending on the actual function.

Transcendental functions are also numberic functions. These include the exponential, logarithm, and trigonometric functions: ACOS, ASIN, COS, SIN, TAN,EXP,LN, LOG and more. As DBAs, we do not use transcendental functions vey much.

Let’s look at numeric functions that are more commonly used by DBAs: **MOD, REMAINDER, ROUND, TRUNC**.

**MOD Function** takes two input values, **a,b** and it returns the remainder of **a** divided by **b**. If b is 0 then MOD returns a, as we cannot divide by 0. So MOD(a,b) will return the remainder of a divided by b, and if b is 0 then it will return a.

**Where do we use the MOD function?**

Usually, we use this function to see if a number is odd or even, or if a number is divisible by another number. Here are some examples of MOD function in the SELECT clause and WHERE clause of a SQL Statement:

1) Return the remainder

```
select mod(3,2) as remainder from dual;
REMAINDER
----------
1
```

2) Check if number is odd or even

```
select case mod(100,2) when 0 then 'Even' else 'Odd' end as number_type
from dual;
NUMBER_TYPE
-----------
Even
```

3) Check if number is divisible by another number:

```
select case mod(100,3)
when 0 then 'Divisible by 3'
else 'Not divisible by 3' end as number_type from dual;
NUMBER_TYPE
------------------
Not divisible by 3
```

4) Return every second row (or third row) in a result set:

```
select last_name, salary, row_number
from ( select last_name, salary, rownum as row_number
from hr.employees order by last_name)
where mod(row_number,2)=0; --returns every second row
```

5) Return only employees with odd employee number:

```
select empno, last_name
from hr.employees
where mod(empno,2)=1; --returns only odd employee numbers
```

Fun Fact: You can use any numeric value in MOD or REMAINDER functions, not just integers!

```
select mod(1.5,2) as remainder from dual;
REMAINDER
---------
1.5
select mod(1.5,1.2) as remainder from dual;
REMAINDER
---------
.3
```

The **REMAINDER **function is similar to MOD function, the difference is that REMAINDER uses ROUND in the formula, versus MOD is using FLOOR to calculate the values.

**ROUND Function**, for numbers, returns the input number rounded to the integer places to the right of the decimal point. So ROUND(a,b) returns the number a, rounded to the b number of right decimal point, where b is an integer: round(5.187,1) = 5.2

If b is not specified (aka is 0) then number a is rounded to zero places. The integer b can also be negative, then a is rounded off to the left of the decimal point.

Examples:

```
select round(20.4591,3) as example1, round(20.4596,3) as example 2 from dual;
EXAMPLE1 EXAMPLE2
-------- --------
20.459 20.46
select round(20.2591,-2) as example3,round(20.2591,-1) as example4 from dual;
EXAMPLE3 EXAMPLE4
-------- --------
0 20
```

**TRUNC function**, for numbers, returns the input number truncated to the integer number of decimal places. So TRUNC(a,b) returns the number a, truncated to the b decimal places. If you omit b or b is 0 then a is truncated to 0 places. B can be negative as well, and then the number a will be truncated to b digits left of the decimal point.

Examples:

```
select trunc(20.4591,2) as example1, trunc(20.4596,3) as example2 from dual;
EXAMPLE1 EXAMPLE2
-------- --------
20.45 20.459
select trunc(20.4591,-1) as example1, trunc(20.4596,-2) as example2 from dual;
EXAMPLE3 EXAMPLE4
-------- --------
20 0
```

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my **FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems**!

**If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com**. **Follow the link to get Today**‘s** Special, only $13.99 CAD !**