select ip, to_number(substr(ip, 1, instr(ip, '.', 1, 1)-1)) newip1, to_number(substr(ip, instr(ip,'.',1,1)+1, instr(ip, '.', 1, 2)-instr(ip,'.',1,1)-1)) newip2, to_number(substr(ip, instr(ip,'.',1,2)+1, instr(ip, '.', 1, 3)-instr(ip,'.',1,2)-1)) newip3, to_number(substr(ip, instr(ip,'.',1,3)+1, length(ip)+1-instr(ip,'.',1,3))) newip4 from (select '10.1.2.123' ip from dual) select regexp_substr(t.ip,'[^.]+',1,1), regexp_substr(t.ip,'[^.]+',1,2), regexp_substr(t.ip,'[^.]+',1,3), regexp_substr(t.ip,'[^.]+',1,4) from (select '10.1.2.123' ip from dual) t