问大家一个函数的问题

Question

现在我有两张表
一张是T1表,有IP 字段(字符串型) 一张是T2表,有IP1,IP2(这两个为一个函数段),NAME,均为字符串类型
比如
T1                                                T2ip                                           ip1                  ip2                  NAME
------                                    -----------           -------------          -------
192.168.114.2                             192.168.114.1          192.168.114.10        某某网通我现在想通过ip能得到    T2表中的NAME
比如ip  是在ip1与ip2之间,所以我取出的结果
ip                         name
------                     --------
192.168.114.2             某某网通请问这样怎么写,
我的想法是
select ip,name from  t1,t2 where Func(ip,ip1,ip2)=true
也就是想自己写一个函数,通过传参在函数内处理,
我函数不熟悉,不知道怎么写,谢谢各位
或者各位有什么好的解决方法,也告诉下,谢谢了

Answer 1

select ip,name from  t1,t2
where t1.ip between t2.ip1 and t2.ip2

Answer 2

select ip,name from t1,t2 where t1.ip>t2.ip1 and t1.ip<t2.ip2;

Answer 3

做一个函数把ip转化为12位的数字，如192.168.114.2 转化为192168114002 
假设此函数为Func()，
然后通过数字的比较得到数据
select ip,name from  t1,t2 where Func(ip)>Func(ip1) and Func(ip)<Func(ip2) 

Answer 4

简单写了下没测试
FUNCTION func (ip in varchar2)RETURN number IS
str varchar2(20);
str1 varchar2(3);
str2 varchar2(3);
str3 varchar2(3);
str4 varchar2(4);
BEGIN
str1:=substr(ip,1,instr(ip,'.')-1);
str:=substr(ip,instr(ip,'.')+1);
str2:=substr(str,1,instr(str,'.')-1);
str:=substr(str,instr(str,'.')+1);
str3:=substr(str,1,instr(str,'.')-1);
str4:=substr(str,instr(str,'.')+1);
return to_number(lpad(str1,3,'0')||lpad(str2,3,'0')||lpad(str3,3,'0')||lpad(str4,3,'0'));
END;

Answer 5

试试这个:
WITH t1 AS (SELECT '192.168.114.2' ip FROM dual UNION ALL SELECT '192.168.114.21' ip FROM dual),
     t2 AS (SELECT '192.168.114.1' ip1,'192.168.114.10' ip2,'某某网通1' NAME FROM dual
            UNION SELECT '192.168.114.12' ip1,'192.168.114.100' ip2,'某某网通2' NAME FROM dual),
     t3 AS (SELECT LPAD(regexp_replace(ip,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\1'),3,'0')||'.'|| 
                  LPAD(regexp_replace(ip,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\2'),3,'0')||'.'|| 
                  LPAD(regexp_replace(ip,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\3'),3,'0')||'.'|| 
                  LPAD(regexp_replace(ip,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\4'),3,'0') ip,ip ipo
                  FROM t1),
     t4 AS (SELECT LPAD(regexp_replace(ip1,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\1'),3,'0')||'.'||
                  LPAD(regexp_replace(ip1,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\2'),3,'0')||'.'||
                  LPAD(regexp_replace(ip1,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\3'),3,'0')||'.'||
                  LPAD(regexp_replace(ip1,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\4'),3,'0') ip1, 
                  LPAD(regexp_replace(ip2,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\1'),3,'0')||'.'||
                  LPAD(regexp_replace(ip2,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\2'),3,'0')||'.'||
                  LPAD(regexp_replace(ip2,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\3'),3,'0')||'.'||
                  LPAD(regexp_replace(ip2,'(\d*)\.(\d*)\.(\d*)\.(\d*)','\4'),3,'0') ip2,NAME
                  FROM t2)
SELECT Ipo ip,NAME FROM t3,t4 where t3.ip>t4.ip1 and t3.ip<t4.ip2;     
--输出:
IP                NAME
192.168.114.21    某某网通2
192.168.114.2      某某网通1