oracle - 将oracle表中的多个逗号分隔值拆分为多行

I Have a problem with oracle split query.

我有oracle拆分查询的问题。

While splitting comma separated data into multiple rows using connect by and regular expression in oracle query I am getting more duplicate rows. for example actually my table having 150 rows in that one two rows having comma separated strings so overall i have to get only 155 rows but i am getting 2000 rows. If i use distinct its working fine but i dont want duplicate rows in query result.

在使用oracle查询中的connect by和regular expression将逗号分隔数据拆分为多行时，我获得了更多的重复行。例如，实际上我的表有150行，在那两行中有逗号分隔的字符串，所以总体来说我只得到155行，但我得到2000行。如果我使用distinct其工作正常但我不想在查询结果中重复行。

I tried the following query however it's generating duplicate rows in query result:

我尝试了以下查询，但它在查询结果中生成重复的行：

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
              UNION 
              SELECT 'f,g',2 from dual
              UNION 
               SELECT 'h',3 FROM DUAL)

SELECT TRIM(REGEXP_SUBSTR( TEMP, '[^,]+', 1, LEVEL)) ,SLNO FROM CTE 
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1

EDIT

编辑

The above select query is only able to split a single comma delimited string, however, it produces duplicate rows when executed on a table with multiple rows. How to restrict the duplicate rows?

上面的select查询只能拆分单个逗号分隔的字符串，但是，当在具有多行的表上执行时，它会生成重复的行。如何限制重复的行？

6 个解决方案

#1

Finally I came up with this answer

最后我想出了这个答案

WITH CTE AS (SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
              UNION
              SELECT 'f,g' temp, 2 slno FROM DUAL
              UNION
              SELECT 'h' temp, 3 slno FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno
FROM CTE
CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
    AND PRIOR slno = slno
    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

#2

Try like this,

试试这样，

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
              UNION 
              SELECT 'f,g',2 from dual
              UNION 
              SELECT 'h',3 FROM DUAL)
     SELECT regexp_substr (temp, '[^,]+', 1, rn)temp, slno
     FROM   cte
     CROSS JOIN 
     (
          SELECT ROWNUM rn
          FROM  (SELECT MAX (LENGTH (regexp_replace (temp, '[^,]+'))) + 1 max_l
                 from cte
                 )
          connect by level <= max_l
         )
     WHERE regexp_substr (temp, '[^,]+', 1, rn) IS NOT NULL
     order by temp;

#3

The accepted answer uses the condition DBMS_RANDOM.VALUE IS NOT NULL which is inappropriate. It just prevents the cyclic loop, however a straight forward question would come up as How and when dbms_random.VALUE can be null? Logically, it will never be NULL.

接受的答案使用条件DBMS_RANDOM.VALUE IS NOT NULL，这是不合适的。它只是阻止循环循环，但是如果dbms_random.VALUE如何以及何时可以为null，则会出现一个直接的问题？从逻辑上讲，它永远不会是NULL。

More appropriate solution is to use sys.odciNumberList and prevent the cyclic loop.

更合适的解决方案是使用sys.odciNumberList并阻止循环循环。

For example,

例如，

Set up

建立

SQL> CREATE TABLE t (
  2    ID          NUMBER GENERATED ALWAYS AS IDENTITY,
  3    text        VARCHAR2(100)
  4  );

Table created.

SQL>
SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word4, word5, word6');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM t;

        ID TEXT
---------- ----------------------------------------------
         1 word1, word2, word3
         2 word4, word5, word6
         3 word7, word8, word9

SQL>

Required query:

必填查询：

SQL> SELECT t.id,
  2         trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
  3  FROM t,
  4    TABLE (CAST (MULTISET
  5    (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1)
  6                 AS sys.odciNumberList
  7                )
  8          ) lines
  9  ORDER BY id
 10  /

        ID TEXT
---------- --------------------------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9

9 rows selected.

An alternate solution using XMLTABLE:

使用XMLTABLE的替代解决方案：

SQL> SELECT id,
  2         trim(COLUMN_VALUE) text
  3  FROM t,
  4    xmltable(('"'
  5    || REPLACE(text, ',', '","')
  6    || '"'))
  7  /

        ID TEXT
---------- ------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9

9 rows selected.

SQL>

There are many ways to achieve the task, like a MODEL clause. For more examples see Split comma delimited strings in a table

有许多方法可以完成任务，比如MODEL子句。有关更多示例，请参阅表中的拆分逗号分隔字符串

#4

without using connect by:

不使用连接：

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
      UNION 
      SELECT 'f,g',2 from dual
      UNION 
       SELECT 'h',3 FROM DUAL
)
,x as (
  select
  ','||temp||',' temp
  ,slno
  from CTE
)
,iter as (SELECT rownum AS pos
    FROM all_objects
)
select
SUBSTR(x.temp
  ,INSTR(x.temp, ',', 1, iter.pos) + 1
  ,INSTR(x.temp, ',', 1, iter.pos + 1)-INSTR(x.temp, ',', 1, iter.pos)-1
) temp
,x.slno
from x, iter
where iter.pos < = (LENGTH(x.temp) - LENGTH(REPLACE(x.temp, ','))) - 1;

#5

Can use the below query to convert comma separated values in rows

可以使用以下查询来转换行中的逗号分隔值

 SELECT trim(x.column_value.extract('e/text()')) COLUMNS
 from t t, table (xmlsequence(xmltype('<e><e>' || replace(valuestring,':','</e><e>')||  
 '</e></e>').extract('e/e'))) x   );

#6

Adding an unique clause does the trick:

添加一个唯一的子句可以解决问题：

    WITH cte AS (
        SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL UNION 
        SELECT 'f,g',2 FROM DUAL UNION SELECT 'h',3 FROM DUAL
    ) SELECT UNIQUE(slno),REGEXP_SUBSTR(temp,'[^,]+', 1, LEVEL)temp FROM cte
    CONNECT BY LEVEL<=REGEXP_COUNT(temp, '[^,]+') ORDER BY slno;

#1