oracle如何截取某个字段?

Question

134.107.116.52:1028
134.107.116.53:1026
134.107.116.67:1026
134.107.116.68:1029
134.107.116.82:1026
134.107.116.85:1028
134.107.116.86:1026
134.107.135.66:1792
134.107.139.14:3427
134.107.139.16:1026
134.107.139.18:1026
134.107.139.40:1028
134.107.139.41:1028
134.107.139.42:1026
134.107.139.43:1028
134.107.139.49:2663
134.107.139.6:44801
134.107.140.12:1028
134.107.140.18:1055
134.107.144.38:1105
134.107.144.42:1081
134.107.145.135:1026
134.107.145.136:1026
134.107.145.138:1026
134.107.145.139:1026
........要把IP地址取出来,去掉端口号.
即取:前面的数据,如何写SQL?

Answer 1

substr(your_column, 1, instr(your_column, ':') - 1)

Answer 2

select substr('134.107.145.139:1026',1, instr('134.107.145.139:1026',':') - 1) from dual;SUBSTR('134.107.145.139:1026',1,INSTR('134.107.145.139:1026',':')-1)
--------------------------------------------------------------------
134.107.145.139 

Answer 3

用SUBSTR()不行,IP地址并非是固定几位,端口也并非固定几位

Answer 4

结贴,给分.
6个月不用SQL取数了,发现INSTR函数不知道用了.呵呵