Oracle如何从字符串中获取IP地址
Tags： Oracle
👉 本文共约608个字，系统预计阅读时间或需3分钟。
SQL问题
您还在为怎么使用SQL从字符串中抽取IP地址发愁吗？某国企的兄弟通过自己的努力可以使用9种不同的方法，从字符串中获取IP地址。
SQL问题：请查询hsql.c_ip表中客户的IP地址，显示格式:cons_id,ip(IP地址示例: 192.168.1.1)
客户IP信息表 (hsql.c_ip) ，表结构及测试数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create table hsql.c_ip( cons_id number, c_ip varchar2(100) ); insert into hsql.c_ip (CONS_ID, C_IP) values (88, '192.168.1.1'); insert into hsql.c_ip (CONS_ID, C_IP) values (999, '192.1683.1.100'); insert into hsql.c_ip (CONS_ID, C_IP) values (1000002, '1#192.168.10.115#1'); insert into hsql.c_ip (CONS_ID, C_IP) values (1000003, '12#192.168.1.115#12'); insert into hsql.c_ip (CONS_ID, C_IP) values (1000004, '123#192.16.1.115#123'); insert into hsql.c_ip (CONS_ID, C_IP) values (1000005, '1234#192.16.111.115#1234'); insert into hsql.c_ip (CONS_ID, C_IP) values (1000006, '12345#192.6.111.115#12345'); insert into hsql.c_ip (CONS_ID, C_IP) values (1000007, '123456#193.16.111.115#123456'); insert into hsql.c_ip (CONS_ID, C_IP) values (1000008, '1234567#191.16.111.115#1234567'); commit; |
业务需求
2016年全国部分企业进行了Oracle SCN隐患治理工作，SCN隐患主要是由于DBLINK “污染”造成，梳理Oracle数据库DBLINK使用逻辑成为了主要工作之一，方法之一就是整理dba_db_links 表HOST字段中记录的对端IP地址，如何从HOST记录的字符串中提取对端IP地址，成为需要解决的问题？
SQL解惑
方法一 正向查找位置
1 2 3 4 5 6 7 | select c_ip, --instr(c_ip, '#', 1, 1), --instr(c_ip, '#', 1, 2), substr(c_ip, instr(c_ip, '#', 1, 1) + 1, instr(c_ip, '#', 1, 2) - instr(c_ip, '#', 1, 1) - 1) from hsql.c_ip; |
方法二 反向查找位置
1 2 3 4 5 6 7 8 | select c_ip, instr(c_ip, '#', 1, 1), instr(c_ip, '#', -1), substr( c_ip,instr(c_ip, '#', 1, 1)+1,instr(c_ip, '#', -1)-instr(c_ip, '#', 1, 1)-1 ), --instr(c_ip, '#', 1, 2), from hsql.c_ip; |
方法三 降级查询（嵌套函数使用）
1 2 3 4 5 | select c.cons_id, substr(substr(c_ip, instr(c_ip, '#') + 1, length(c_ip)), 0, instr(substr(c_ip, instr(c_ip, '#') + 1, length(c_ip)), '#') - 1) ip from hsql.c_ip c; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
方法四 子表查询
1 2 3 | select c_ip,substr(c_ip,c_start+1,c_end-c_start-1),c_start,c_end from ( select c_ip,instr(c_ip,'#',1,1) c_start,instr(c_ip,'#',1,2) c_end from hsql.c_ip ); |
方法五 正则表达式1
1 | select t.c_ip,regexp_substr(t.c_ip,'[1-9]*[.][1-9]*[.][0-9]*[.][1-9]*') c_IP from hsql.c_ip t; |
方法六 正则表达式2
1 | select t.c_ip,regexp_substr(t.c_ip,'([0-9]*[.]){3}[1-9]*') c_IP from hsql.c_ip t; |
方法七 正则表达式3
1 | select t.c_ip,regexp_substr(t.c_ip,'(\d*[.]){3}\d*') c_IP from hsql.c_ip t; |
方法八 自定义函数
函数：
1 2 3 4 5 6 7 8 | create or replace function getIp(v_ip in varchar2) return varchar2 is Result varchar2(200); begin select regexp_substr(v_ip,'(\d*[.]){3}\d*') into Result from dual; return(Result); end getIp; 测试方法： select p.cons_id, p.c_ip,getip(p.c_ip) from hsql.c_ip p where p.cons_id=1000002; |
方法九 视图法
1 2 | create view hsql.v_c_ip as select p.cons_id cons_id,p.c_ip c_ip,regexp_substr(p.c_ip,'(\d*[.]){3}\d*') ip from hsql.c_ip p; |
测试方法：
1 | select tt.cons_id, tt.ip from hsql.v_c_ip tt; |
