SQL解惑-如何從字串中獲取IP地址

orastar發表於2017-06-14


1、SQL問題



您還在為怎麼使用SQL從字串中抽取IP地址發愁嗎?某國企的兄弟透過自己的努力可以使用9種不同的方法,從字串中獲取IP地址。


SQL問題:請查詢hsql.c_ip表中客戶的IP地址,顯示格式:cons_id,ip(IP地址示例: 192.168.1.1)

客戶IP資訊表 (hsql.c_ip) ,表結構及測試資料:


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;



2、業務需求



2016年全國部分企業進行了Oracle SCN隱患治理工作,SCN隱患主要是由於DBLINK “汙染”造成,梳理Oracle資料庫DBLINK使用邏輯成為了主要工作之一,方法之一就是整理dba_db_links 表HOST欄位中記錄的對端IP地址,如何從HOST記錄的字串中提取對端IP地址,成為需要解決的問題?


3、SQL解惑





方法一 正向查詢位置

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;


方法二 反向查詢位置

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;


方法三 降級查詢(巢狀函式使用)

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;


方法四 子表查詢

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

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

select t.c_ip,regexp_substr(t.c_ip,'([0-9]*[.]){3}[1-9]*') c_IP from hsql.c_ip t;

方法七 正規表示式3

select t.c_ip,regexp_substr(t.c_ip,'(\d*[.]){3}\d*') c_IP from hsql.c_ip t;


方法八 自定義函式

函式:

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;


方法九 檢視法

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;


測試方法:

select tt.cons_id, tt.ip from hsql.v_c_ip tt;


如何您還有更好的方法,請給我們留言,期待您的回覆!!!!

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31442014/viewspace-2140717/,如需轉載,請註明出處,否則將追究法律責任。

相關文章