数据库版本oracle 10.2.0.1.0。表中存在一名为URL的列,长度VARCHAR2(1024). 此列记录的是一个http地址,其中一定包括“k”这个参数,但参数的位置以及值的内容及长度都不固定。
例如:
http://...com?k=abc123df...&x=88dd... (k参数在最前面)
或
http://...com?a=848d&k=abc123df...&x=88dd... (k参数在中间)
或
http://...com?a=848d&k=abc123df (k参数在最后)现在想编写一个sql获得k参数的值,想用正则表达式写,但无奈真的不会,特来请教,请高手帮忙,多谢!~
例如:
http://...com?k=abc123df...&x=88dd... (k参数在最前面)
或
http://...com?a=848d&k=abc123df...&x=88dd... (k参数在中间)
或
http://...com?a=848d&k=abc123df (k参数在最后)现在想编写一个sql获得k参数的值,想用正则表达式写,但无奈真的不会,特来请教,请高手帮忙,多谢!~
你试试
select case
when instr( regexp_replace(字段名,'(.+k=)(.+)','\2'),'&') >0
then regexp_replace(字段名,'(.+k=)(.+)(&.*)','\2')
else regexp_replace(字段名,'(.+k=)(.+)','\2')
end from 表名
case
when instr(字段名,'k=')>0 then
case
when instr( regexp_replace(字段名,'(.+k=)(.+)','\2'),'&') >0 then
regexp_replace(字段名,'(.+k=)(.+)(&.*)','\2')
else
regexp_replace(字段名,'(.+k=)(.+)','\2')
end
else
''
end
from 表名
CREATE TABLE URL_LIST
(
ID NUMBER(20) NOT NULL,
DATE DATE,
URL VARCHAR2(300 BYTE),
)url值示例:
http://l.cpec.com/f?m=u87y34f&n=554&f=8744&o=0234&w=0&k=gary021&t=http://erty.map.com/mmm/D/2587/20110506/JFIELJFEIJSIFE_/p457/58/http://www.mkf.org/brssen.html?me=kwss想通过查询获得gary021
scott@TBWORA> create table t(id number(18,0), http varchar2(1000));表已创建。scott@TBWORA> set define off;
scott@TBWORA> insert into t(id,http) values(1,'http://l.cpec.com/f?m=u87y34f\&n=554\&f=8744&o=0234\&w=0\&k=gary021\&t=http://erty.map.com/mmm/D/2587/20110506/JFIELJFEIJSIFE_/p457/58/http://www.mkf.org/brssen.html?me=kwss');已创建 1 行。scott@TBWORA> select id, REPLACE(REPLACE(REGEXP_SUBSTR(http,'&k=.*?&'),'&k=',''),'\&','') AS k
2 from t; ID K
---------- --------------
1 gary021
dexter@ORCL> create table URLVIEW
2 (
3 VURL VARCHAR2(2000)
4 );Table created.dexter@ORCL> insert into URLVIEW (VURL)
2 values ('http://...com?k=abc123df'||chr(38)||'x=88dd...');1 row created.dexter@ORCL> insert into URLVIEW (VURL)
2 values ('http://...com?a=848d'||chr(38)||'k=abc123df'||chr(38)||'x=88dd');1 row created.dexter@ORCL> insert into URLVIEW (VURL)
2 values ('http://...com?a=848d'||chr(38)||'k=abc123df');1 row created.dexter@ORCL> insert into URLVIEW (VURL)
2 values ('http://...com?a=848d');1 row created.dexter@ORCL> insert into URLVIEW (VURL)
2 values ('http://l.cpec.com/f?m=u87y34f'||chr(38)||'n=554'||chr(38)||'f=8744
'||chr(38)||'o=0234'||chr(38)||'w=0'||chr(38)||'k=gary021'||chr(38)||'t=http://e
rty.map.com/mmm/D/2587/20110506/JFIELJFEIJSIFE_/p457/58/http://www.mkf.org/brsse
n.html?me=kwss');1 row created.dexter@ORCL> commit;Commit complete.dexter@ORCL> select
2 case
3 when instr(vurl,'k=')>0 then
4 case
5 when instr( regexp_replace(vurl,'(.+k=)(.+)','\2'),'&') >0 then
6 regexp_replace(vurl,'(.+k=)(.+)(&.*)','\2')
7 else
8 regexp_replace(vurl,'(.+k=)(.+)','\2')
9 end
10 else
11 ''
12 end
13 from urlView
14 ;CASEWHENINSTR(VURL,'K=')>0THENCASEWHENINSTR(REGEXP_REPLACE(VURL,'(.+K=)(.+)','\2
'),'&')>0THENREGEXP_
--------------------------------------------------------------------------------
--------------------
abc123df
abc123df
abc123dfgary021dexter@ORCL>
select substr(substr(VURL,
instr(VURL, 'k=') + 2),
0,
instr(substr(VURL,
instr(VURL, 'k=') + 2),
'&&') - 1)
from URLVIEW;