## Finding the Roots of a Number

Moderator: NorbertKrupa

JimKnicely Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

### Finding the Roots of a Number

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!
Jim Knicely Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.