Arithmetic Functions in SQL

Many of the uses we have for the data we retrieve involve mathematics. Most implementations of SQL provide arithmetic functions similar to the functions covered here.

Arithmetic Functions

The examples in this blog use the NUMCHAR table:

SQL> SELECT * FROM NUMCHAR;
ABLASTNAMEFIRSTNAMEMCODECHARCODE
3.14154PURVISKELLYA3215
-45.707TAYLORCHUCKJ6730
59CHRISTINELAURAC6525
-57.66742ADAMSFESTERM8740
1555COSTALESARMANDOA7735
-7.25.3KONGMAJORG5220

ABS

The ABS function returns the absolute value of the number we point to. For example:

SQL> SELECT ABS(A) ABSOLUTE_VALUE FROM NUMCHAR;
ABSOLUTE_VALUE
--------------
3.1415
45
5
57.667
15
7.2

ABS changes all the negative numbers to positive and leaves positive numbers alone.

CEIL and FLOOR

CEIL returns the smallest integer greater than or equal to its argument. FLOOR does just the reverse, returning the largest integer equal to or less than its argument. For example:

SQL> SELECT B, CEIL(B) CEILING FROM NUMCHAR;

| B    | CEILING |
| ---- | ------- |
| 4    | 4       |
| .707 | 1       |
| 99   | 42      |
| 42   | 55      |
| 55   | 55      |
| 5.3  | 6       |

And

SQL> SELECT A, FLOOR(A) FLOOR FROM NUMCHAR;

| A       | FLOOR |
| ------- | ----- |
| 3.1415  | 3     |
| -45     | -45   |
| 5       | 5     |
| -57.667 | -58   |
| 15      | 15    |
| -7.2    | -8    |

COS, COSH, SIN, SINH, TAN, and TANH

The COS, SIN, and TAN functions provide support for various trigonometric concepts. They all work on the assumption that n is in radians. The following statement returns some unexpected values if we don’t realize COS expects A to be in radians.

SQL> SELECT A, COS(A) FROM NUMCHAR;

| A       | COS(A)    |
| ------- | --------- |
| 3.1415  | -1        |
| -45     | .52532199 |
| 5       | .28366219 |
| -57.667 | .437183   |
| 15      | -.7596879 |
| -7.2    | .60835131 |

Analysis: We would expect the COS of 45 degrees to be in the neighborhood of .707, not .525. To make this function work the way we would expect it to in a degree-oriented world, we need to convert degrees to radians. (When was the last time we heard a news broadcast report that a politician had done a pi-radian turn? We hear about a 180-degree turn.) Because 360 degrees - 2 pi radians, we can write

SQL> SELECT A, COS(A* 0.01745329251994) FROM NUMCHAR;
|
| A       | COS(A*0.01745329251994) |
| ------- | ----------------------- |
| 3.1415  | .99849724               |
| -45     | .70710678               |
| 5       | .9961947                |
| -57.667 | .5348391                |
| 15      | .96592583               |
| -7.2    | .9921147                |

Analysis:

Note that the number 0.01745329251994 is radians divided by degrees. The trigonometric functions work as follows:

SQL> SELECT A, COS(A*0.017453), COSH(A*0.017453) FROM NUMCHAR;

| A       | COS(A*0.017453) | COSH(A*0.017453) |
| ------- | --------------- | ---------------- |
| 3.1415  | .99849729       | 1.0015035        |
| -45     | .70711609       | 1.3245977        |
| 5       | .99619483       | 1.00381          |
| -57.667 | .53485335       | 1.5507072        |
| 15      | .96592696       | 1.0344645        |
| -7.2    | .99211497       | 1.0079058        |

And

SQL> SELECT A, SIN(A*0.017453), SINH(A*0.017453) FROM NUMCHAR;

| A       | SIN(A*0.017453) | SINH(A*0.017453) |
| ------- | --------------- | ---------------- |
| 3.1415  | .05480113       | .05485607        |
| -45     | -.7070975       | -.8686535        |
| 5       | .08715429       | .0873758         |
| -57.667 | -.8449449       | -1.185197        |
| 15      | .25881481       | .26479569        |
| -7.2    | -.1253311       | -.1259926        |

And

SQL> SELECT A, TAN(A*0.017453), TANH(A*0.017453) FROM NUMCHAR;

| A       | TAN(A*0.017453) | TANH(A*0.017453) |
| ------- | --------------- | ---------------- |
| 3.1415  | .05488361       | .05477372        |
| -45     | -.9999737       | -.6557867        |
| 5       | .08748719       | .08704416        |
| -57.667 | -1.579769       | -.7642948        |
| 15      | .26794449       | .25597369        |
| -7.2    | -.1263272       | -.1250043        |

EXP

EXP enables we to raise e (e is a mathematical constant used in various formulas) to a power. Here’s how EXP raises e by the values in column A:

SQL> SELECT A, EXP(A) FROM NUMCHAR;

| A       | EXP(A)    |
| ------- | --------- |
| 3.1415  | 23.138549 |
| -45     | 2.863E-20 |
| 5       | 148.41316 |
| -57.667 | 9.027E-26 |
| 15      | 3269017.4 |
| -7.2    | .00074659 |

LN and LOG

These two functions center on logarithms. LN returns the natural logarithm of its argument. For example:

SQL> SELECT A, LN(A) FROM NUMCHAR;
ERROR:
ORA-01428: argument '-45' is out of range

Did we neglect to mention that the argument had to be positive? Write

SQL> SELECT A, LN(ABS(A)) FROM NUMCHAR;

| A       | LN(ABS(A)) |
| ------- | ---------- |
| 3.1415  | 1.1447004  |
| -45     | 3.8066625  |
| 5       | 1.6094379  |
| -57.667 | 4.0546851  |
| 15      | 2.7080502  |
| -7.2    | 1.974081   |

Analysis: Notice how we can embed the function ABS inside the LN call. The other logarith-mic function, LOG, takes two arguments, returning the logarithm of the first argument in the base of the second. The following query returns the logarithms of column B in base 10.

SQL> SELECT B, LOG(B, 10) FROM NUMCHAR;

| B    | LOG(B,10) |
| ---- | --------- |
| 4    | 1.660964  |
| .707 | -6.640962 |
| 9    | 1.0479516 |
| 42   | .61604832 |
| 55   | .57459287 |
| 5.3  | 1.3806894 |

MOD

We have encountered MOD before. On Day 3, Expressions, Conditions, and Operators, we saw that the ANSI standard for the modulo operator % is sometimes implemented as the function MOD. Here’s a query that returns a table showing the remainder of A divided by B:

SQL> SELECT A, B, MOD(A,B) FROM NUMCHAR;

| A       | B    | MOD(A,B) |
| ------- | ---- | -------- |
| 3.1415  | 4    | 3.1415   |
| -45     | .707 | -.459    |
| 5       | 9    | 5        |
| -57.667 | 42   | -15.667  |
| 15      | 55   | 15       |
| -7.2    | 5.3  | -1.9     |

POWER

To raise one number to the power of another, use POWER. In this function the first argument is raised to the power of the second:

SQL> SELECT A, B, POWER(A,B) FROM NUMCHAR;
ERROR:
ORA-01428: argument '-45' is out of range

Analysis: ***### At first glance we are likely to think that the first argument can’t be negative. But that impression can’t be true, because a number like -4 can be raised to a power. Therefore, if the first number in the POWER function is negative, the second must be an integer. We can work around this problem by using CEIL (or FLOOR):

SQL> SELECT A, CEIL(B), POWER(A,CEIL(B)) FROM NUMCHAR;

| A       | CEIL(B) | POWER(A,CEIL(B)) |
| ------- | ------- | ---------------- |
| 3.1415  | 4       | 97.3976          |
| -45     | 1       | -45              |
| 5       | 9       | 1953125          |
| -57.667 | 42      | 9.098E+73        |
| 15      | 55      | 4.842E+64        |
| -7.2    | 6       | 139314.07        |

SIGN

SIGN returns -1 if its argument is less than 0, 0 if its argument is equal to 0, and 1 if its argument is greater than 0, as shown in the following example:

SQL> SELECT A, SIGN(A) FROM NUMCHAR;

| A       | SIGN(A) |
| ------- | ------- |
| 3.1415  | 1       |
| -45     | -1      |
| 5       | 1       |
| -57.667 | -1      |
| 15      | 1       |
| -7.2    | -1      |
| 0       | 0       |

We could also use SIGN in a SELECT WHERE clause like this:

SQL> SELECT A  FROM NUMCHAR WHERE SIGN(A) - 1;
A
---------
3.1415
5
15

SQRT

The function SQRT returns the square root of an argument. Because the square root of a negative number is undefined, we cannot use SQRT on negative numbers.

SQL> SELECT A, SQRT(A) FROM NUMCHAR;
ERROR:
ORA-01428: argument '-45' is out of range

However, we can fix this limitation with ABS:

SQL> SELECT ABS(A), SQRT(ABS(A)) FROM NUMCHAR;

| ABS(A) | SQRT(ABS(A)) |
| ------ | ------------ |
| 3.1415 | 1.7724277    |
| 45     | 6.7082039    |
| 5      | 2.236068     |
| 57.667 | 7.5938791    |
| 15     | 3.8729833    |
| 7.2    | 2.6832816    |
| 0      | 0            |

TRUNC

The TRUNC function truncates the column, expression, or value to n decimal places. The TRUNC function works with arguments similar to those of the ROUND function. If the second argument is 0 or is missing, the value is truncated to zero decimal places. It the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argument is -2, the value is rounded to two decimal places to the left.

SQL> SELECT A, TRUNC(A) FROM NUMCHAR;

| A      | TRUNC(A) |
| ------ | -------- |
| 3.1415 | 3        |
| -45    | -45      |
| 55     | -57.667  |
| -57    | -57      |
| 15     | 15       |
| -7.2   | -7       |

sql rdbms arithmetic-function

Subscribe For More Content