在后台处理简单的数字代码识别

Question

请教，如下
6401090201;6402;640202;640203;640204;640298;640299;6405这行数字代码分别用“；”号隔开，我要以分号为基础，把数字代码识别出来，类型为字符型请问我该用哪个函数，要自动循环，全部找出来，只要数字代码，不要分号，分号只是起到分隔作用。谢谢

Answer 1

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

Answer 2

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

Answer 3

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> 

Answer 4

: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 6401090201
2 6402
3 640202
4 640203
5 640204
6 640298
7 640299
8 6405