Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Mysql Function To Rounding number to nearest 1,10,100,1000

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 1.62k
    Comment on it

    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)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: