在“xx.yy"附近获得“以逗号分隔的列表无效"使用 dbms_utility.comma_to_table
我有这样的字符串:str:='ac_Abc.88,ac_Abc.99,ac_Abc.77'.用逗号(，)分割后我需要得到第一个元素.所以我像这样使用:
I have string like this: str:='ac_Abc.88,ac_Abc.99,ac_Abc.77'. I need to get first element after splitting with comma(,). So im using using like this:
str VARCHAR2(500);
dbms_utility.comma_to_table
( list => regexp_replace(str,'(^|,)','1')
, tablen => l_count
, tab => l_array
);
我收到以下错误:
ORA-20001: comma-separated list invalid near bc.88
ORA-06512: at "SYS.DBMS_UTILITY", line 239
ORA-06512: at "SYS.DBMS_UTILITY", line 272
但如果我有这样的字符串 str:='ac_Abc88,ac_Abc99,ac_Abc77'，同样的方法工作正常并给我预期的结果.
But if i have string like this, str:='ac_Abc88,ac_Abc99,ac_Abc77', the same method working fine and giving me expected results.
所以我想有一些需要更正的地方来考虑."作为常规字符.你能建议我如何解决这个问题.
So i guess there is something need to be corrected to consider "." as regular character. Can you please suggest how can i solve this.
推荐答案
参见 如何将逗号分隔的字符串拆分成行
1.REGEXP_SUBSTR 方法
SQL> WITH DATA AS(
2 SELECT 'ac_Abc.88,ac_Abc.99,ac_Abc.77' str FROM dual)
3 SELECT regexp_substr(str,'[^,]+',1,level) str
4 FROM DATA
5 CONNECT BY regexp_substr(str, '[^,]+', 1, level) IS NOT NULL
6 /
STR
-----------------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77
SQL>
2.XML 方法
SQL> SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () str
2 FROM
3 (SELECT XMLTYPE ( '<rows><row>'
4 || REPLACE ('ac_Abc.88,ac_Abc.99,ac_Abc.77', ',', '</row><row>')
5 || '</row></rows>' ) AS xmlval
6 FROM DUAL
7 ) x,
8 TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d
9 /
STR
--------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77
3.表函数
SQL> CREATE TYPE test_type
2 AS
3 TABLE OF VARCHAR2(100)
4 /
Type created.
SQL>
SQL> CREATE OR REPLACE
2 FUNCTION comma_to_table(
3 p_list IN VARCHAR2)
4 RETURN test_type
5 AS
6 l_string VARCHAR2(32767) := p_list || ',';
7 l_comma_index PLS_INTEGER;
8 l_index PLS_INTEGER := 1;
9 l_tab test_type := test_type();
10 BEGIN
11 LOOP
12 l_comma_index := INSTR(l_string, ',', l_index);
13 EXIT
14 WHEN l_comma_index = 0;
15 l_tab.EXTEND;
16 l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
17 l_index := l_comma_index + 1;
18 END LOOP;
19 RETURN l_tab;
20 END comma_to_table;
21 /
Function created.
SQL> sho err
No errors.
SQL>
SQL> SELECT * FROM TABLE(comma_to_table('ac_Abc.88,ac_Abc.99,ac_Abc.77'))
2 /
COLUMN_VALUE
--------------------------------------------------------------------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77
SQL>
4.流水线函数
SQL> CREATE OR REPLACE
2 FUNCTION comma_to_table(
3 p_list IN VARCHAR2)
4 RETURN test_type PIPELINED
5 AS
6 l_string LONG := p_list || ',';
7 l_comma_index PLS_INTEGER;
8 l_index PLS_INTEGER := 1;
9 BEGIN
10 LOOP
11 l_comma_index := INSTR(l_string, ',', l_index);
12 EXIT
13 WHEN l_comma_index = 0;
14 PIPE ROW ( SUBSTR(l_string, l_index, l_comma_index - l_index) );
15 l_index := l_comma_index + 1;
16 END LOOP;
17 RETURN;
18 END comma_to_table;
19 /
Function created.
SQL> sho err
No errors.
SQL>
SQL> SELECT * FROM TABLE(comma_to_table('ac_Abc.88,ac_Abc.99,ac_Abc.77'))
2 /
COLUMN_VALUE
--------------------------------------------------------------------------------
ac_Abc.88
ac_Abc.99
ac_Abc.77
相关文章