在后台处理简单的数字代码识别 请教,如下6401090201;6402;640202;640203;640204;640298;640299;6405这行数字代码分别用“;”号隔开,我要以分号为基础,把数字代码识别出来,类型为字符型请问我该用哪个函数,要自动循环,全部找出来,只要数字代码,不要分号,分号只是起到分隔作用。谢谢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 SELECT DECODE (INSTR (in_cid, ';', 1, 1), 0, in_cid, SUBSTR (in_cid, DECODE (ROWNUM, 1, 1, INSTR (in_cid, ';', 1, ROWNUM - 1 ) + 1 ), DECODE (ROWNUM, 1, INSTR (in_cid, ';', 1, 1) - 1, LENGTH (in_cid) - LENGTH (REPLACE (in_cid, ';', '' ) ) + 1, LENGTH (in_cid), INSTR (in_cid, ';', 1, ROWNUM) - 1 - INSTR (in_cid, ';', 1, ROWNUM - 1 ) ) ) ) cid FROM DUAL CONNECT BY ROWNUM <= LENGTH (in_cid) - LENGTH (REPLACE (in_cid, ';', '')) + 1 SELECT DECODE (INSTR (:in_string, ';', 1, 1), 0, :in_string, SUBSTR (:in_string, DECODE (ROWNUM, 1, 1, INSTR (:in_string, ';', 1, ROWNUM - 1 ) + 1 ), DECODE (ROWNUM, 1, INSTR (:in_string, ';', 1, 1) - 1, LENGTH (:in_string) - LENGTH (REPLACE (:in_string, ';', '' ) ) + 1, LENGTH (:in_string), INSTR (:in_string, ';', 1, ROWNUM) - 1 - INSTR (:in_string, ';', 1, ROWNUM - 1 ) ) ) ) cid FROM DUAL CONNECT BY ROWNUM <= LENGTH (:in_string) - LENGTH (REPLACE (:in_string, ';', '')) + 1 SQL> set serveroutput on;SQL> SQL> declare 2 s nvarchar2(100); 3 s1 nvarchar2(30); 4 begin 5 s:= '6401090201;6402;640202;640203;640204;640298;640299;6405'; 6 while instr(s,';')>0 7 loop 8 s1:= substr(s,1,instr(s,';')-1); 9 s := substr(s,instr(s,';')+1,length(s)-instr(s,';')); 10 dbms_output.put_line(s1); 11 end loop; 12 dbms_output.put_line(s); 13 end; 14 /640109020164026402026402036402046402986402996405PL/SQL procedure successfully completedSQL> :in_string=6401090201;6402;640202;640203;640204;640298;640299;6405 SELECT DECODE (INSTR (:in_string, ';', 1, 1), 0, :in_string, SUBSTR (:in_string, DECODE (ROWNUM, 1, 1, INSTR (:in_string, ';', 1, ROWNUM - 1 ) + 1 ), DECODE (ROWNUM, 1, INSTR (:in_string, ';', 1, 1) - 1, LENGTH (:in_string) - LENGTH (REPLACE (:in_string, ';', '' ) ) + 1, LENGTH (:in_string), INSTR (:in_string, ';', 1, ROWNUM) - 1 - INSTR (:in_string, ';', 1, ROWNUM - 1 ) ) ) ) cid FROM DUAL CONNECT BY ROWNUM <= LENGTH (:in_string) - LENGTH (REPLACE (:in_string, ';', '')) + 1结果Row# CID1 64010902012 64023 6402024 6402035 6402046 6402987 6402998 6405 求SQL语句。。。 如何学习Oracle? select 如何查询量个表并加查询23小时前的数据 oracle 10g Enterprise Manager 点登陆后还是回到登陆界面 层次查询SQL语句 存储过程里如何知道update后是否成功更新了数据,更新了多少条? 请问下面的更新语句怎么写? 系统视图问题 clob字段大小如何设置?? 很多人没注意的select into 问题讨论 这样的进程可以杀死吗 关于oracle中的sequence的问题
SELECT DECODE (INSTR (in_cid, ';', 1, 1),
0, in_cid,
SUBSTR (in_cid,
DECODE (ROWNUM,
1, 1,
INSTR (in_cid,
';',
1,
ROWNUM - 1
)
+ 1
),
DECODE (ROWNUM,
1, INSTR (in_cid, ';', 1, 1) - 1,
LENGTH (in_cid)
- LENGTH (REPLACE (in_cid,
';',
''
)
)
+ 1, LENGTH (in_cid),
INSTR (in_cid, ';', 1, ROWNUM)
- 1
- INSTR (in_cid,
';',
1,
ROWNUM - 1
)
)
)
) cid
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (in_cid)
- LENGTH (REPLACE (in_cid, ';', ''))
+ 1
SELECT DECODE (INSTR (:in_string, ';', 1, 1),
0, :in_string,
SUBSTR (:in_string,
DECODE (ROWNUM,
1, 1,
INSTR (:in_string,
';',
1,
ROWNUM - 1
)
+ 1
),
DECODE (ROWNUM,
1, INSTR (:in_string, ';', 1, 1) - 1,
LENGTH (:in_string)
- LENGTH (REPLACE (:in_string,
';',
''
)
)
+ 1, LENGTH (:in_string),
INSTR (:in_string, ';', 1, ROWNUM)
- 1
- INSTR (:in_string,
';',
1,
ROWNUM - 1
)
)
)
) cid
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (:in_string)
- LENGTH (REPLACE (:in_string, ';', ''))
+ 1
SQL> set serveroutput on;
SQL>
SQL> declare
2 s nvarchar2(100);
3 s1 nvarchar2(30);
4 begin
5 s:= '6401090201;6402;640202;640203;640204;640298;640299;6405';
6 while instr(s,';')>0
7 loop
8 s1:= substr(s,1,instr(s,';')-1);
9 s := substr(s,instr(s,';')+1,length(s)-instr(s,';'));
10 dbms_output.put_line(s1);
11 end loop;
12 dbms_output.put_line(s);
13 end;
14 /6401090201
6402
640202
640203
640204
640298
640299
6405PL/SQL procedure successfully completedSQL>
0, :in_string,
SUBSTR (:in_string,
DECODE (ROWNUM,
1, 1,
INSTR (:in_string,
';',
1,
ROWNUM - 1
)
+ 1
),
DECODE (ROWNUM,
1, INSTR (:in_string, ';', 1, 1) - 1,
LENGTH (:in_string)
- LENGTH (REPLACE (:in_string,
';',
''
)
)
+ 1, LENGTH (:in_string),
INSTR (:in_string, ';', 1, ROWNUM)
- 1
- INSTR (:in_string,
';',
1,
ROWNUM - 1
)
)
)
) cid
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (:in_string)
- LENGTH (REPLACE (:in_string, ';', ''))
+ 1
结果Row# CID1 6401090201
2 6402
3 640202
4 640203
5 640204
6 640298
7 640299
8 6405