oracle – Dividi più valori separati da virgola nella tabella di Oracle a più righe

Ho un problema con la query split di Oracle.

Mentre dividi i dati separati da virgola in più righe usando connect by e regular expression nella query di Oracle, sto ottenendo più righe duplicate . ad esempio, in realtà la mia tabella ha 150 righe in quella due righe con stringhe separate da virgole, quindi nel complesso devo ottenere solo 155 righe ma sto ottenendo 2000 righe. Se uso distinto funziona bene, ma non voglio le righe duplicate nel risultato della query.

Ho provato la seguente query, tuttavia sta generando righe duplicate nel risultato della 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 

MODIFICARE

La query di selezione sopra è in grado di dividere una sola stringa delimitata da una virgola , tuttavia produce righe duplicate quando viene eseguita su una tabella con più righe . Come limitare le righe duplicate?

Finalmente ho trovato questa risposta

 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 

Prova così,

 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; 

La risposta accettata utilizza la condizione DBMS_RANDOM.VALUE IS NOT NULL che è inappropriata. Impedisce solo il ciclo ciclico, tuttavia una domanda diretta verrebbe visualizzata come Come e quando dbms_random.VALUE può essere nullo? Logicamente, non sarà mai NULL .

La soluzione più appropriata è usare sys.odciNumberList e prevenire il ciclo ciclico.

Per esempio,

Impostare

 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> 

Query richiesta:

 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. 

Una soluzione alternativa che utilizza 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> 

Ci sono molti modi per raggiungere l'objective, come una clausola MODELLO . Per ulteriori esempi, vedere Dividere le stringhe delimitate da virgole in una tabella

senza usare connetti da :

 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; 

È ansible utilizzare la query seguente per convertire valori separati da virgola in righe

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

L’aggiunta di una clausola univoca fa il trucco:

  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;