具体函数如下,可以直接在plsql中使用,来源于网络,我只是修改了正则表达式,具体链接找不到了,抱歉!
create or replace function ip2number(ip varchar2)
return number
is
ip_num_hex varchar2(100);
begin
if (regexp_like(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$')) then
ip_num_hex := lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\1'), 'XX')),2,'0') ||
lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\2'), 'XX')),2,'0') ||
lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\3'), 'XX')),2,'0') ||
lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\4'), 'XX')),2,'0');
return to_number(ip_num_hex, 'XXXXXXXX');
else
return -1;
end if;
exception
when others then
return -99999999999;
end;
测试:
select ip2number('169.254.55.6') from dual;
结果
2852009734
同样的原理,可以用正则表达式匹配的方式截取IP的前几个位段,例如截取前三个位段:
select regexp_substr('168.225.0.1','(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.',1,1) from dual;
结果:
168.225.0.