You could use regexp_substr
:
select ip,
regexp_substr(ip, 'd+',1,1) as first_octet,
regexp_substr(ip, 'd+',1,2) as second_octet,
regexp_substr(ip, 'd+',1,3) as third_octet,
regexp_substr(ip, 'd+',1,4) as fourth_octet
from (select '10.20.30.40' AS ip from dual )ips;
Rextester Demo
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…