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;
| A | B | LASTNAME | FIRSTNAME | M | CODE | CHARCODE |
|---|---|---|---|---|---|---|
| 3.1415 | 4 | PURVIS | KELLY | A | 32 | 15 |
| -45 | .707 | TAYLOR | CHUCK | J | 67 | 30 |
| 5 | 9 | CHRISTINE | LAURA | C | 65 | 25 |
| -57.667 | 42 | ADAMS | FESTER | M | 87 | 40 |
| 15 | 55 | COSTALES | ARMANDO | A | 77 | 35 |
| -7.2 | 5.3 | KONG | MAJOR | G | 52 | 20 |
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 |