如何将逗号分隔的字符串转换为数组？
我有输入’1,2,3’，我需要将其转换为数组。
Oracle提供了一个内置函数：dbms_utility.comma_to_table(
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_util.htm#ARPLS73224)。
不幸的是，这个不适用于数字：
sql> declare 2 l_input varchar2(4000) := '1,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”作为前缀元素，它的工作原理：
sql> declare 2 l_input varchar2(4000) := '1,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,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.
问候，抢。
