This chapter is from the book
This chapter is from the book
This chapter is from the book
Numeric Functions
Numeric functions allow for manipulation of numeric values. Numeric functions are sometimes called mathematical functions. The functions we’ll cover are ROUND, RAND, PI, and POWER.
The ROUND function allows you to round any numeric value. The general format is:
ROUND(NumericValue , DecimalPlaces)
The NumericValue argument can be any positive or negative number, with or without decimal places, such as 712.863 or –42. The DecimalPlaces argument is trickier. It can contain a positive or negative integer, or zero. If DecimalPlaces is a positive integer, it means to round to that many decimal places. If DecimalPlaces is a negative integer, it means to round to that number of positions to the left of the decimal place. The following chart shows how the number 712.863 is rounded, with different values for the DecimalPlaces argument.
ROUND Function Expression |
Resulting Value |
ROUND(712.863, 3) |
712.863 |
ROUND(712.863, 2) |
712.860 |
ROUND(712.863, 1) |
712.900 |
ROUND(712.863, 0) |
713.000 |
ROUND(712.863, –1) |
710.000 |
ROUND(712.863, –2) |
700.000 |
The PI function merely returns the value of the mathematical number pi. As you may remember from high school geometry, the number pi is an irrational number approximated by the value 3.14. This function is seldom used, but nicely illustrates the point that numeric functions need not have any arguments. For example, the statement:
SELECT PI()
returns the value 3.14159265358979. To take this example a little further, let’s say that we want the value of pi rounded to two decimal places. This can be accomplished by creating a composite function with the PI and ROUND functions. The PI function is used to get the initial value, and the ROUND function is added to round it to two decimal places. The following statement returns a value of 3.14:
SELECT ROUND(PI(),2)
The final numeric function we’ll cover, which is much more commonly used than PI, is POWER. The POWER function is used to specify a numeric value that includes exponents. The general format of the function is:
POWER(NumericValue, Exponent)
Let’s start with an example that illustrates how to display the number 5 raised to the second power. This is commonly referred to as “5 squared.” The SELECT statement:
SELECT POWER(5,2) AS '5 Squared'
returns this data:
In this example, 5 is the numeric value to be evaluated, and 2 is the value of the exponent. Remembering that the square root of a number can be expressed as an exponent with a decimal value less than 1, we can calculate the square root of 25 as follows. The statement:
SELECT POWER(25,.5) AS 'Square Root of 25'
returns this data:
In algebraic terms, the calculation takes 25 to the 1/2 (or .5) power. This is the same as taking the square root of 25.