在 PL/SQL 中将逗号分隔的字符串转换为数组

2021-12-05 00:00:00 tokenize oracle plsql

如何将逗号分隔的字符串转换为数组?

How do I convert a comma separated string to a array?

我有输入 '1,2,3' ，我需要把它转换成一个数组.

I have the input '1,2,3' , and I need to convert it into an array.

推荐答案

Oracle 提供了内置函数 DBMS_UTILITY.COMMA_TO_TABLE.

Oracle provides the builtin function DBMS_UTILITY.COMMA_TO_TABLE.

不幸的是，这个不适用于数字:

Unfortunately, this one doesn't work with numbers:

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => l_input
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        l_array(i)
 18      );
 19    end loop;
 20  end;
 21  /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 132
ORA-06512: at "SYS.DBMS_UTILITY", line 164
ORA-06512: at "SYS.DBMS_UTILITY", line 218
ORA-06512: at line 6

但是有一个小技巧，用'x'前缀元素，它的工作原理:

But with a little trick to prefix the elements with an 'x', it works:

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => regexp_replace(l_input,'(^|,)','1x')
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        substr(l_array(i),2)
 18      );
 19    end loop;
 20  end;
 21  /
3
Element 1 of array contains: 1
Element 2 of array contains: 2
Element 3 of array contains: 3

PL/SQL procedure successfully completed.

问候，罗布.

相关文章