Oracle- 拆分字符串逗号分隔(字符串包含空格和连续逗号) [英] Oracle- Split string comma delimited (string contains spaces and consecutive commas)

问题描述

我找不到有关如何在 ORACLE 中拆分逗号分隔字符串的解决方案.搜索了很多，对我的情况没有任何作用

I can't find a solution about how to split a comma-delimited string in ORACLE. Searched a lot, nothing works for my case

代码

DECLARE
  TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY_OF_VARCHAR;
  MY_STRING VARCHAR2(2000) := '12 3,456,,abc,def';
BEGIN
 FOR CURRENT_ROW IN (
    with test as    
      (select MY_STRING from dual)
      select regexp_substr(MY_STRING, '[^,]+', 1, rownum) SPLIT
      from test
      connect by level <= length (regexp_replace(MY_STRING, '[^,]+'))  + 1)

  LOOP
   DBMS_OUTPUT.PUT_LINE('>' || CURRENT_ROW.SPLIT || '<');
   --DBMS_OUTPUT.PUT_LINE(CURRENT_ROW.SPLIT);
    MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Array Size:' || MY_ARRAY.COUNT);
END;

/

输出为:

>12 3<
>456<
>abc<
>def<
><
Array Size:5

空值乱序！！！！

推荐答案

试试这个来解析列表部分.它处理NULLS:

Try this for the parsing the list part. It handles NULLS:

SQL> select regexp_substr('12 3,456,,abc,def', '(.*?)(,|$)', 1, level, null, 1) SPLIT, level
    from dual
    connect by level <= regexp_count('12 3,456,,abc,def',',') + 1
    ORDER BY level;

SPLIT                  LEVEL
----------------- ----------
12 3                       1
456                        2
                           3
abc                        4
def                        5

SQL>

不幸的是，当您搜索用于解析列表的正则表达式时，您总会发现这种不处理空值的形式，应避免使用:'[^,]+'.请参阅此处了解更多信息:将逗号分隔的值拆分为列在 Oracle 中.

Unfortunately when you search for regex's for parsing lists, you will always find this form which does NOT handle nulls and should be avoided: '[^,]+'. See here for more info: Split comma separated values to columns in Oracle.

这篇关于Oracle- 拆分字符串逗号分隔(字符串包含空格和连续逗号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！