Finding the Roots of a Number

[JimKnicely]

Most databases have a built in function we can use to find the square root of a number. But what about the cube root?

Vertica:

Vertica provides the SQRT() function to find the square root and the CBRT() function to find the cube root of a number.

Code: Select all

dbadmin=> SELECT sqrt(9) "Square Root", cbrt(27) "Cube Root";
Square Root | Cube Root
-------------+-----------
           3 |         3
(1 row)

Vertica, like PostgreSQL, also provides some cool mathematical operators for finding the square root and cube root:

Code: Select all

dbadmin=> SELECT |/9 "Square Root", ||/27 "Cube Root";
Square Root | Cube Root
-------------+-----------
           3 |         3
(1 row)

MySQL:

MySQL delivers a SQRT() function to find the square root but not a function dedicated to finding the cube root of a number. However, we can use the POW() function to approximate the cube root or a number and then round the result:

Code: Select all

MySQL> SELECT sqrt(9) "Square Root", round(pow(27, 1/3)) "Cube Root";
+-------------+-----------+
| Square Root | Cube Root |
+-------------+-----------+
|           3 |         3 |
+-------------+-----------+
1 row in set (0.00 sec)

SQL Server:

SQL Server also has the SQRT() function for square roots but no CBRT() function. However, we can use the POWER() function in a similar to how we did it in MySQL (see above):



Oracle:

Oracle has the built in function SQRT() for square roots but not one for cube roots. As we did in MySQL and SQL Server we can use the POWER() function to find the cube root:

Code: Select all

SQL> SELECT sqrt(9) "Square Root", round(power(27, 1/3),0) "Cube Root"
  2    FROM dual;

Square Root  Cube Root
----------- ----------
          3          3

Have fun!