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

CREATE PROCEDURE bulk_processing_example( )
 MODIFIES SQL DATA
BEGIN
 DECLARE delete_count INT DEFAULT 0;
 DECLARE last_row INT DEFAULT 0;
 DECLARE l_rec_id INT;

 DECLARE c1 CURSOR FOR SELECT rec_id FROM log_archive;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row=1;

 OPEN c1;
 MainLoop:
 LOOP
 FETCH c1 INTO l_rec_id;
 IF last_row THEN
 LEAVE MainLoop;
 END IF;
 IF purge_due(l_rec_id) THEN
 DELETE FROM log_archive WHERE rec_id=l_rec_id;
 SET delete_count=delete_count+1;
 IF MOD(delete_count,100)=0 THEN
 COMMIT;
 END IF;
 END IF;
 END LOOP MainLoop;
 CLOSE c1;

END;

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

CREATE PROCEDURE select_winner( )
 READS SQL DATA
BEGIN
 DECLARE winner_id INT;
 DECLARE max_employee_id INT;
 DECLARE winner_name VARCHAR(70);

 SELECT MAX(employee_id)
 INTO max_employee_id
 FROM employees;

 SET winner_id=FLOOR(RAND( )*max_employee_id)+1;

 SELECT CONCAT_WS(' ','Employee of the week is',firstname,surname)
 FROM employees
 WHERE employee_id=winner_id;
END;

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!

Table 9-2. Additional numeric functions

Function	Syntax	Description
ACOS	number1=ACOS(number2)	Arc cosine of a number.
ASIN	number1=ASIN(number2)	Arc sine of a number.
ATAN	number1=ATAN(number2)	Arc tangent of a number.
COT	number1=COT(number2)	Cotangent of a number.
CRC32	number=CRC32(string)	Cyclic redundancy check value for a string.
DEGREES	degrees=DEGREES(radians)	Converts radians to degrees.
EXP	number1=EXP(number2)	Natural logarithm (base e) to the power of a number.
LN	number1=LN(number2)	Natural logarithm of a number.
LOG	number1=LOG(number2,base)	Logarithm of a number in the base specified.
LOG10	number=LOG10(number2)	Base 10 logarithm of a number.
LOG2	number1=LOG2(number)	Base 2 logarithm of a number.
PI	number=PI( )	Returns the value of PI.
RADIANS	radians=RADIANS(degrees)	Converts radians to degrees.
SIN	number1=SIN(number2)	Sine of a number (expressed in radians).
TAN	number1=TAN(number2)	Tangent of a number expressed in radians.