SQL分解含IP的字段

将一个IP地址字段分解到列中，如121.32.3.4

要得到如下结果：

 A               B               C                D

121            32              3                4
Mysql方案：

select substring_index(substring_index(y.ip,’.’,1),’.’,-1) a,
     substring_index(substring_index(y.ip,’.’,2),’.’,-1) b,
     substring_index(substring_index(y.ip,’.’,3),’.’,-1) c,
     substring_index(substring_index(y.ip,’.’,4),’.’,-1) d
from (select ‘121.32.3.4’ as ip from temp) y

Oracle方案：

select ip,
     substr(ip, 1, instr(ip, ’.’)-1) a,
     substr(ip, instr(ip, ’.’)+1, instr(ip, ’.’,1,2)- instr(ip, ’.’)-1) b,
     substr(ip, instr(ip, ’.’,1,2)+1, instr(ip, ’.’,1,3)- instr(ip, ’.’,1,2)-1) c,
     substr(ip, instr(ip, ’.’,1,3)+1) d
from (select ‘121.32.3.4’ as ip from temp)