Below is the Mysql Function To Rounding number to nearest 1,10,100,1000
DELIMITER $$
DROP FUNCTION IF EXISTS `rounding_to_nearest`$$
CREATE FUNCTION `rounding_to_nearest`(num decimal(15,2),round_to int,nearest varchar(10)) RETURNS decimal(15,2)
BEGIN
DECLARE return_value decimal(15,2);
CASE nearest
WHEN 'none' THEN
set return_value = num;
WHEN 'up' THEN
CASE round_to
WHEN 1 THEN
set return_value = CEILING(num/1);
WHEN 10 THEN
set return_value = CEILING(num/10)*10;
WHEN 100 THEN
set return_value = CEILING(num/100)*100;
END CASE;
When 'down' Then
CASE round_to
WHEN 1 THEN
set return_value = FLOOR(num/1);
WHEN 10 THEN
set return_value = FLOOR(num/10)*10;
WHEN 100 THEN
set return_value = FLOOR(num/100)*100;
END CASE;
END CASE;
return return_value;
END$$
DELIMITER ;
**NOTE:**
**num** is number to be round off
**round_to** one of parameter[1,10,100,1000]
**nearest** one of parameter['up','down','none']
To test the function
select rounding(1234.87,1,'up');
>> 1235.00
0 Comment(s)