SQL> select substr('http://www.csdn.net/point.aspx',12,8 ) from dual;SUBSTR('HTTP://WWW.CSDN.NET/PO ---------------------------------------------------------------- csdn.netSQL>
select substr(str,instr(str,'w',1,3)+1,(instr(str,'/',1,3)-instr(str,'/',1,2)-4)) from dual
--做了个取二级域名的函数,请LZ参考一下:SQL> CREATE OR REPLACE FUNCTION f_twodomain(http0 IN varchar2) RETURN varchar2 IS 2 v_str_twodomain varchar2(80); 3 begin 4 For i in 12..100 5 Loop 6 if ascii(substr(http0,i,1))=47 then 7 return v_str_twodomain; 8 end if; 9 v_str_twodomain:=v_str_twodomain || substr(http0,i,1); 10 End loop; 11 end; 12 /Function createdSQL> select f_twodomain('http://www.csdn.net/point.aspx') from dual;F_TWODOMAIN('HTTP://WWW.CSDN.N -------------------------------------------------------------------------------- csdn.netSQL>
10g的方法: SELECT RTRIM(LTRIM(REGEXP_SUBSTR('http://www.csdn.net/point.aspx', '\..+/'), '.'), '/') FROM DUAL;
SQL> select substr('http://www.csdn.net/point.aspx',12,8 ) from dual;SUBSTR('HTTP://WWW.CSDN.NET/PO
----------------------------------------------------------------
csdn.netSQL>
select substr(str,instr(str,'w',1,3)+1,(instr(str,'/',1,3)-instr(str,'/',1,2)-4)) from dual
--做了个取二级域名的函数,请LZ参考一下:SQL> CREATE OR REPLACE FUNCTION f_twodomain(http0 IN varchar2) RETURN varchar2 IS
2 v_str_twodomain varchar2(80);
3 begin
4 For i in 12..100
5 Loop
6 if ascii(substr(http0,i,1))=47 then
7 return v_str_twodomain;
8 end if;
9 v_str_twodomain:=v_str_twodomain || substr(http0,i,1);
10 End loop;
11 end;
12 /Function createdSQL> select f_twodomain('http://www.csdn.net/point.aspx') from dual;F_TWODOMAIN('HTTP://WWW.CSDN.N
--------------------------------------------------------------------------------
csdn.netSQL>
SELECT RTRIM(LTRIM(REGEXP_SUBSTR('http://www.csdn.net/point.aspx', '\..+/'),
'.'),
'/')
FROM DUAL;
instr('http://www.csdn.net/point.aspx', '.', 1),
instr('http://www.csdn.net/point.aspx', '/', 1, 3) -
instr('http://www.csdn.net/point.aspx', '.', 1))
FROM dual;
1.5楼的正则,这个比较酷.
2.用substr+instr,无非就是第一个.和第三个/之间的串.
这个是对的,谢谢了,不过没有看明白。
大家帮忙解释一下吧,
还有,我想或者的是“.csdn.net” 就是前面还有一个点。。
谢谢
'/')
FROM DUAL;