How can I easily convert 139.29.0.0 to 139.029.000.000 at sql?

Question

How can I easily convert 139.29.0.0 to 139.029.000.000 at sql?

Answer 1

One option would be splitting by the dots, padding leading zeroes upto the 3 precision and then aggregating back such as

 SELECT LISTAGG( LPAD( REGEXP_SUBSTR(n,'[^.]+',1,level), 3, '0'), '.' ) WITHIN GROUP (ORDER BY level) AS IP
   FROM t 
CONNECT BY level <= REGEXP_COUNT(n,'.')+1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR n = n
  GROUP BY n   

Demo