Finding the Roots of a Number

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Finding the Roots of a Number

Post by JimKnicely » Wed Jan 23, 2013 9:35 pm

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):

Image

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”