Oracle - Split multiple comma-separated values ​​in oracle table into multiple rows

Question

I have a problem requesting an oracle. A.

When splitting comma-separated data into multiple lines with connect by and a regular expression in an oracle query, I get more repeated lines . for example, actually my table has 150 rows in that two rows have comma-separated rows, so in general I need to get only 155 rows, but I get 2000 rows. If I use excellent work, but I do not want to duplicate rows as a result of the query.

I tried the following query, however it generated duplicate rows as a result of the query:

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 able to split a single-comma row, however it creates duplicate rows when executed in a table with multiple rows . How to limit duplicate lines?

Answer 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 

Answer 2

Try it,

 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; 

Answer 3

The accepted answer uses the condition DBMS_RANDOM.VALUE IS NOT NULL , which is inappropriate. This simply prevents a loopback, however a direct question will arise how and when can dbms_random.VALUE be null? Logically, it will never be NULL .

A more appropriate solution is to use sys.odciNumberList and prevent looping.

For example,

Customization

 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 Request:

 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. 

Alternative solution using 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, for example, MODEL . For more examples, see Separate Comma Separated Rows in a Table

Answer 4

without using connect :

 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; 

Answer 5

You can use the following query to convert comma separated values ​​to strings

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

Answer 6

Adding a unique offer 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;