Numeric Functions

Numeric functions perform operations on numeric data types such as INT and FLOAT.

9.2.1. ABS

number1 =ABS (number2 )

ABS returns the absolute value of a numberthat is, the magnitude of the value ignoring any minus sign.

SET var1=ABS(2.143); 2.143 SET var2=ABS(-10); 10 SET var3=ABS(10); 10 SET var4=ABS(-2.3); 2.3

9.2.2. BIN

binary_number =BIN (decimal_number )

BIN returns the binary (base 2) representation of an integer value.

SET var1=BIN(1); 1 SET var2=BIN(2); 10 SET var3=BIN(3); 11 SET var4=BIN(45); 101101

9.2.3. CEILING

number1 =CEILING (number2 )

CEILING returns the next integer number that is higher than the input floating-point number.

SET var1=CEILING(3.5); 4 SET var2=CEILING(-3.5); -3

9.2.4. CONV

number1=CONV(number2,from_base,to_base)

CONV converts numbers from one base system to another. Although CONV is, in essence, a numeric function, it may return values that you may need to deal with as strings (e.g., hexadecimal numbers).

The following CONV statements convert the number 45 (base 10) into binary (base 2), hexadecimal (base 16), and octal (base 8):

SET var1=CONV(45,10,2); 101101 SET var2=CONV(45,10,16); 2D SET var3=CONV(45,10,8) ; 55

These statements convert the number 45 (base 2) into base 10, and converts 45 (base 8) into base 2:

SET var4=CONV(101101,2,10); 45 SET var5=CONV(55,8,2); 101101

9.2.5. FLOOR

number1 =FLOOR (number2 )

FLOOR returns the largest integer value not greater than X.

SET var1=FLOOR(3.5); 3 SET var2=FLOOR(-3.5); -4

9.2.6. FORMAT

string =FORMAT (number,decimal_places )

FORMAT returns a string representation of a number with comma separators at each thousand and with the specified number of decimal places.

SET var1=FORMAT(21321.3424,2); 21,321.34

9.2.7. HEX

HexNumber =HEX (DecimalNumber )

HEX returns the hexadecimal representation of a number.

SET var1=HEX(9); 9 SET var2=HEX(11); B SET var3=HEX(32); 20

9.2.8. LEAST

number1 =LEAST (number , number2 [,..])

LEAST returns the number in the input series with the smallest numerical value.

SET var1=LEAST(32,432,-2,-1.4); -2

9.2.9. MOD

remainder =MOD (numerator ,denominator )

MOD returns the remainder (modulus) when the first number is divided by the second number.

MOD is particularly handy when you want something to happen at regular intervals in a loop. For instance, Example 9-8 purges (deletes) rows from the LOG_ARCHIVE table based on some criteria. As we discuss in Chapter 22, reducing commit frequency is an important optimization for transactional storage engines such as InnoDB. However, we do want to commit at regular intervals; otherwise, we risk losing all the work if the program fails midway through execution.

So Example 9-8 calculates the modulus of the delete count divided by 100. If this modulus is 0which happens every 100 rowsa COMMIT is issued. The end result is that the program commits the delete operations every 100 rows.

Example 9-8. Using the MOD function to perform periodic COMMITs

You can also calculate a modulus using numerator%denominator or numerator MOD denominator. Thus, these three assignments are all equivalent:

SET var1=MOD(5,3); 2 SET var2=5%3; 2 SET var3=5 MOD 3 ; 2

9.2.10. POWER

result =POWER (number ,power )

POWER returns the result of raising the first number to the power of the second number. You can use POW as a synonym for POWER.

SET var1=POWER(3,2); 9 (3*3) SET var2=POWER(2,3); 8 (2*2*2) SET var3=POWER(4,.5); 2 (square root of 4) SET var4=POWER(10,-2); 0.01 SET var5=POWER(10,-3); 0.001 SET var6=POW(2,2); 4

9.2.11. RAND

number=RAND([seed])

RAND returns a random floating-point number between 0 and 1. If seed is specified, it is used to initialize the random-number generator, which lets you avoid generating repeatable sequences.

SET var1=RAND( ); 0.86494333191304 SET var2=RAND( ); 0.96148952838172 SET var3=RAND(5); 0.40613597483014 SET var4=RAND( ); 0.21261767690314 SET var5=RAND(5) ; 0.40613597483014 SET var6=RAND( ); 0.17861983010417

RAND can be used within stored programs to generate or select random table data. For instance, in Example 9-9, we use the RAND function to randomly select the employee of the week (and you thought we based it on performance!). We first find the maximum employee_id and then generate a random number between 1 and that number. Since RAND returns a floating-point number between 0 and 1, we multiply that number by the maximum employee number, generating a number between 0 and the maximum employee number. Next, we use FLOOR to convert the number to an integer value, and then add 1 to avoid generating an employee_id of 0.

Example 9-9. Using the RAND function to retrieve random rows

9.2.12. ROUND

integer =ROUND (number [,decimals ])

ROUND converts a floating-point number to the nearest integer value orif the second argument is specifiedto the specified number of decimal points.

SET var1=PI( ); 3.141593 SET var2=ROUND(PI( )); 3 SET var3=ROUND(PI( ),4); 3.1416 SET var5=ROUND(4.49); 4 SET var6=ROUND(4.51); 5

9.2.13. SIGN

number1 =SIGN (number2 )

SIGN returns -1 if a number is less than 0, 0 if the number is 0, and 1 if the number is greater than 0.

SET var1=SIGN(-5); -1 SET var2=SIGN(0); 0 SET var3=SIGN(5); 1

9.2.14. SQRT

number1 =SQRT (number2 )

SQRT returns the square root of a number. It is equivalent to POWER(number,.5).

SET var1=SQRT(4); 2 SET var2=SQRT(64); 8 SET var3=POWER(64,.5); 8

9.2.15. Other Numeric Functions

Table 9-2 lists additional numeric functions. These functions are rarely used in mainstream MySQL applications; in this category are the trigonometric and logarithmic functions that you probably studied in high school and have never used since!