oracle 9i字符串转多列,如何在oracle 9i中最佳拆分csv字符串

这是三个示例：

1)使用dbms_utility.comma_to_table。这不是通用例程，因为元素应该是有效的标识符。通过一些肮脏的技巧，我们可以使其更通用：

SQL> declare

2    cn_non_occuring_prefix constant varchar2(4) := 'zzzz';

3    mystring varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example

4    l_tablen binary_integer;

5    l_tab    dbms_utility.uncl_array;

6  begin

7    dbms_utility.comma_to_table

8    ( list   => cn_non_occuring_prefix || replace(mystring,':',','||cn_non_occuring_prefix)

9    , tablen => l_tablen

10    , tab    => l_tab

11    );

12    for i in 1..l_tablen

13    loop

14      dbms_output.put_line(substr(l_tab(i),1+length(cn_non_occuring_prefix)));

15    end loop;

16  end;

17  /

a

sd

dfg

31456

dasd

sdfsdf

PL/SQL-procedure is geslaagd.

2)使用SQL的连接级别。如果您使用的是10g或更高版本，则可以将按级别连接方法与正则表达式结合使用，如下所示：

SQL> declare

2    mystring varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example

3  begin

4    for r in

5    ( select regexp_substr(mystring,'[^:]+',1,level) element

6        from dual

7     connect by level <= length(regexp_replace(mystring,'[^:]+')) + 1

8    )

9    loop

10      dbms_output.put_line(r.element);

11    end loop;

12  end;

13  /

a

sd

dfg

31456

dasd

sdfsdf

PL/SQL-procedure is geslaagd.

3)再次使用SQL的逐级连接，但是现在结合使用旧的SUBSTR / INSTR(如果您使用的是版本9)，例如：

SQL> declare

2    mystring varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example

3  begin

4    for r in

5    ( select substr

6             ( str

7             , instr(str,':',1,level) + 1

8             , instr(str,':',1,level+1) - instr(str,':',1,level) - 1

9             ) element

10        from (select ':' || mystring || ':' str from dual)

11     connect by level <= length(str) - length(replace(str,':')) - 1

12    )

13    loop

14      dbms_output.put_line(r.element);

15    end loop;

16  end;

17  /

a

sd

dfg

31456

dasd

sdfsdf

PL/SQL-procedure is geslaagd.

您可以在此博客文章中看到更多类似的技术：http: //rwijk.blogspot.com/2007/11/interval-based-row-generation.html

希望这可以帮助。

问候，罗布。

要发表您的评论：

将分隔的值插入规范化表的示例。

首先创建表：

SQL> create table csv_table (col)

2  as

3  select 'a,sd,dfg,31456,dasd,,sdfsdf' from dual union all

4  select 'a,bb,ccc,dddd' from dual union all

5  select 'zz,yy,' from dual

6  /

Table created.

SQL> create table normalized_table (value varchar2(10))

2  /

Table created.

因为您似乎对dbms_utility.comma_to_table方法感兴趣，所以在这里提到它。但是，由于标识符的怪癖和逐行处理的缓慢，我当然不建议使用此变体。

SQL> declare

2    cn_non_occuring_prefix constant varchar2(4) := 'zzzz';

3    l_tablen binary_integer;

4    l_tab    dbms_utility.uncl_array;

5  begin

6    for r in (select col from csv_table)

7    loop

8      dbms_utility.comma_to_table

9      ( list   => cn_non_occuring_prefix || replace(r.col,',',','||cn_non_occuring_prefix)

10      , tablen => l_tablen

11      , tab    => l_tab

12      );

13      forall i in 1..l_tablen

14        insert into normalized_table (value)

15        values (substr(l_tab(i),length(cn_non_occuring_prefix)+1))

16      ;

17    end loop;

18  end;

19  /

PL/SQL procedure successfully completed.

SQL> select * from normalized_table

2  /

VALUE

----------

a

sd

dfg

31456

dasd

sdfsdf

a

bb

ccc

dddd

zz

yy

14 rows selected.

我确实推荐这种SQL变体：

SQL> truncate table normalized_table

2  /

Table truncated.

SQL> insert into normalized_table (value)

2   select substr

3          ( col

4          , instr(col,',',1,l) + 1

5          , instr(col,',',1,l+1) - instr(col,',',1,l) - 1

6          )

7     from ( select ',' || col || ',' col from csv_table )

8        , ( select level l from dual connect by level <= 100 )

9    where l <= length(col) - length(replace(col,',')) - 1

10  /

14 rows created.

SQL> select * from normalized_table

2  /

VALUE

----------

a

a

zz

sd

bb

yy

dfg

ccc

31456

dddd

dasd

sdfsdf

14 rows selected.

问候，罗布。