DB2 implementation of INET_NTOA MySQL function

Here's a sample implementation of MySQL INET_NTOA function in DB2. If you are going to use this solution, you may need to ensure that valid values are passed. It doesn't make any validation of the input data provided.


CREATE OR REPLACE FUNCTION INET_NTOA (N BIGINT)
 RETURNS varchar(16)
 LANGUAGE SQL
 DETERMINISTIC
 NO EXTERNAL ACTION

BEGIN ATOMIC
DECLARE m1, tmp, i, j BIGINT default 0;
DECLARE ip varchar(16) default '';

 SET M1 = N;
 set i =0;
 WHILE (i < 4) DO   
    set j =0;
     WHILE (j < 8) DO
        if mod(m1,2) = 1 then
            set tmp = tmp + power(2, j);
        end if;
        set m1 = m1/2;
        set j = j + 1;
    END WHILE;
    set ip = tmp || '.' || ip;
    set tmp = 0;
    set i = i + 1;
 END WHILE;
 set ip = substr(ip, 1, length(ip) - 1);
 return ip;
END

Sample usage:
SELECT INET_NTOA(167773449) FROM DUAL

Output: 10.0.5.9