Divisione della stringa in più righe in Oracle

So che a questo è stata data una risposta in qualche modo con PHP e MYSQL, ma mi chiedevo se qualcuno potesse insegnarmi l’approccio più semplice per dividere una stringa (delimitata da virgole) in più righe in Oracle 10g (preferibilmente) e 11g.

La tabella è la seguente:

Name | Project | Error 108 test Err1, Err2, Err3 109 test2 Err1 

Voglio creare il seguente:

 Name | Project | Error 108 Test Err1 108 Test Err2 108 Test Err3 109 Test2 Err1 

Ho visto alcune potenziali soluzioni attorno allo stack, tuttavia hanno rappresentato solo una singola colonna (essendo la stringa delimitata da virgole). Qualsiasi aiuto sarebbe molto apprezzato.

La risposta accettata ha scarse prestazioni quando si utilizzano set di dati di grandi dimensioni.

Questo potrebbe essere un modo migliore (anche con espressioni regolari e connessione con):

 with temp as ( select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error from dual union all select 109, 'test2', 'Err1' from dual ) select distinct t.name, t.project, trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) as error from temp t, table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.error, '[^,]+')) + 1) as sys.OdciNumberList)) levels order by name 

EDIT : Ecco una spiegazione semplice (come in, non in profondità) della query.

  1. length (regexp_replace(t.error, '[^,]+')) + 1 usa regexp_replace per cancellare tutto ciò che non è il delimitatore (virgola in questo caso) e la length +1 per ottenere quanti elementi (errori) ci sono.
  2. Il select level from dual connect by level <= (...) utilizza una query gerarchica per creare una colonna con un numero crescente di corrispondenze trovate, da 1 al numero totale di errori.

    Anteprima:

     select level, length (regexp_replace('Err1, Err2, Err3', '[^,]+')) + 1 as max from dual connect by level <= length (regexp_replace('Err1, Err2, Err3', '[^,]+')) + 1 
  3. table(cast(multiset(.....) as sys.OdciNumberList)) esegue alcuni tipi di casting di oracle.
    • Il cast(multiset(.....)) as sys.OdciNumberList trasforma più raccolte (una raccolta per ogni riga nel set di dati originale) in un'unica raccolta di numeri, OdciNumberList.
    • La funzione table() trasforma una collezione in un set di risultati.
  4. FROM senza un join crea un cross join tra il set di dati e il multiset. Di conseguenza, una riga nel set di dati con 4 corrispondenze verrà ripetuta 4 volte (con un numero crescente nella colonna denominata "column_value").

    Anteprima:

     select * from temp t, table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.error, '[^,]+')) + 1) as sys.OdciNumberList)) levels 
  5. trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) usa il valore column_value come parametro nth_appearance / ocurrence per regexp_substr .
  6. Puoi aggiungere alcune altre colonne dal tuo set di dati ( t.name, t.project come esempio) per una facile visualizzazione.

Alcuni riferimenti ai documenti Oracle:

  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • Costanti, tipi e mappature di estensibilità (OdciNumberList)
  • CAST (multiset)
  • Query gerarchiche

le espressioni regolari sono una cosa meravigliosa 🙂

 with temp as ( select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error from dual union all select 109, 'test2', 'Err1' from dual ) SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str FROM (SELECT Name, Project, Error str FROM temp) t CONNECT BY instr(str, ',', 1, level - 1) > 0 order by Name 

C’è un’enorme differenza tra i due seguenti:

  • dividere una singola stringa delimitata
  • divisione di stringhe delimitate per più righe in una tabella.

Se non si limitano le righe, la clausola CONNECT BY produrrebbe più righe e non darà l’output desiderato.

  • Per la stringa singola delimitata, guarda la stringa delimitata da una singola virgola in righe
  • Per dividere le stringhe delimitate in una tabella, guarda le stringhe delimitate da una virgola in una tabella

Oltre alle espressioni regolari , alcune altre alternative stanno usando:

  • XMLTable
  • Clausola MODELLO

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> 

Utilizzando 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> 

Utilizzando la clausola MODELLO :

 SQL> WITH 2 model_param AS 3 ( 4 SELECT id, 5 text AS orig_str , 6 ',' 7 || text 8 || ',' AS mod_str , 9 1 AS start_pos , 10 Length(text) AS end_pos , 11 (Length(text) - Length(Replace(text, ','))) + 1 AS element_count , 12 0 AS element_no , 13 ROWNUM AS rn 14 FROM t ) 15 SELECT id, 16 trim(Substr(mod_str, start_pos, end_pos-start_pos)) text 17 FROM ( 18 SELECT * 19 FROM model_param MODEL PARTITION BY (id, rn, orig_str, mod_str) 20 DIMENSION BY (element_no) 21 MEASURES (start_pos, end_pos, element_count) 22 RULES ITERATE (2000) 23 UNTIL (ITERATION_NUMBER+1 = element_count[0]) 24 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1, 25 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) 26 ) 27 WHERE element_no != 0 28 ORDER BY mod_str , 29 element_no 30 / ID TEXT ---------- -------------------------------------------------- 1 word1 1 word2 1 word3 2 word4 2 word5 2 word6 3 word7 3 word8 3 word9 9 rows selected. SQL> 

Un paio di altri esempi dello stesso:

 SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab FROM dual CONNECT BY LEVEL <= regexp_count('Err1, Err2, Err3', ',')+1 / SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab FROM dual CONNECT BY LEVEL <= length('Err1, Err2, Err3') - length(REPLACE('Err1, Err2, Err3', ',', ''))+1 / 

Inoltre, potresti utilizzare DBMS_UTILITY.comma_to_table e table_to_comma: http://www.oracle-base.com/articles/9i/useful-procedures-and-functions-9i.php#DBMS_UTILITY.comma_to_table

Vorrei proporre un approccio diverso usando una funzione tabella PIPELINED. È in qualche modo simile alla tecnica di XMLTABLE, tranne per il fatto che stai fornendo la tua funzione personalizzata per dividere la stringa di caratteri:

 -- Create a collection type to hold the results CREATE OR REPLACE TYPE typ_str2tbl_nst AS TABLE OF VARCHAR2(30); / -- Split the string according to the specified delimiter CREATE OR REPLACE FUNCTION str2tbl ( p_string VARCHAR2, p_delimiter CHAR DEFAULT ',' ) RETURN typ_str2tbl_nst PIPELINED AS l_tmp VARCHAR2(32000) := p_string || p_delimiter; l_pos NUMBER; BEGIN LOOP l_pos := INSTR( l_tmp, p_delimiter ); EXIT WHEN NVL( l_pos, 0 ) = 0; PIPE ROW ( RTRIM( LTRIM( SUBSTR( l_tmp, 1, l_pos-1) ) ) ); l_tmp := SUBSTR( l_tmp, l_pos+1 ); END LOOP; END str2tbl; / -- The problem solution SELECT name, project, TRIM(COLUMN_VALUE) error FROM t, TABLE(str2tbl(error)); 

risultati:

  NAME PROJECT ERROR ---------- ---------- -------------------- 108 test Err1 108 test Err2 108 test Err3 109 test2 Err1 

Il problema con questo tipo di approccio è che spesso l’ottimizzatore non conoscerà la cardinalità della funzione tabella e dovrà indovinare. Questo potrebbe essere potenzialmente dannoso per i piani di esecuzione, quindi questa soluzione può essere estesa per fornire statistiche di esecuzione per l’ottimizzatore.

Puoi vedere questa stima dell’ottimizzatore eseguendo un PIANO DI ESPLO nella query sopra:

 Execution Plan ---------------------------------------------------------- Plan hash value: 2402555806 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16336 | 366K| 59 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 16336 | 366K| 59 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | T | 2 | 42 | 3 (0)| 00:00:01 | | 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 28 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- 

Anche se la raccolta ha solo 3 valori, l’ottimizzatore ha stimato 8168 righe per esso (valore predefinito). Questo può sembrare irrilevante all’inizio, ma potrebbe essere sufficiente che l’ottimizzatore decida per un piano non ottimale.

La soluzione è utilizzare le estensioni optimizer per fornire statistiche per la raccolta:

 -- Create the optimizer interface to the str2tbl function CREATE OR REPLACE TYPE typ_str2tbl_stats AS OBJECT ( dummy NUMBER, STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList ) RETURN NUMBER, STATIC FUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo, p_stats OUT SYS.ODCITabFuncStats, p_args IN SYS.ODCIArgDescList, p_string IN VARCHAR2, p_delimiter IN CHAR DEFAULT ',' ) RETURN NUMBER ); / -- Optimizer interface implementation CREATE OR REPLACE TYPE BODY typ_str2tbl_stats AS STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList ) RETURN NUMBER AS BEGIN p_interfaces := SYS.ODCIObjectList ( SYS.ODCIObject ('SYS', 'ODCISTATS2') ); RETURN ODCIConst.SUCCESS; END ODCIGetInterfaces; -- This function is responsible for returning the cardinality estimate STATIC FUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo, p_stats OUT SYS.ODCITabFuncStats, p_args IN SYS.ODCIArgDescList, p_string IN VARCHAR2, p_delimiter IN CHAR DEFAULT ',' ) RETURN NUMBER AS BEGIN -- I'm using basically half the string lenght as an estimator for its cardinality p_stats := SYS.ODCITabFuncStats( CEIL( LENGTH( p_string ) / 2 ) ); RETURN ODCIConst.SUCCESS; END ODCIStatsTableFunction; END; / -- Associate our optimizer extension with the PIPELINED function ASSOCIATE STATISTICS WITH FUNCTIONS str2tbl USING typ_str2tbl_stats; 

Test del piano di esecuzione risultante:

 Execution Plan ---------------------------------------------------------- Plan hash value: 2402555806 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 23 | 59 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 23 | 59 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | T | 2 | 42 | 3 (0)| 00:00:01 | | 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 1 | 2 | 28 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- 

Come puoi vedere la cardinalità del piano sopra non è più il valore di 8196 indovinato. Non è ancora corretto perché stiamo passando una colonna anziché una stringa letterale alla funzione.

Alcuni ritocchi al codice funzione sarebbero necessari per dare una stima più ravvicinata in questo caso particolare, ma penso che il concetto generale sia praticamente spiegato qui.

La funzione str2tbl utilizzata in questa risposta è stata originariamente sviluppata da Tom Kyte: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Il concetto di associare statistiche con tipi di oggetti può essere ulteriormente esplorato leggendo questo articolo: http://www.oracle-developer.net/display.php?id=427

La tecnica qui descritta funziona in 10g +.

REGEXP_COUNT non è stato aggiunto fino a Oracle 11i. Ecco una soluzione Oracle 10g, adottata dalla soluzione di Art.

 SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab FROM dual CONNECT BY LEVEL <= LENGTH('Err1, Err2, Err3') - LENGTH(REPLACE('Err1, Err2, Err3', ',', '')) + 1; 

Penso che il modo migliore in cui mi connetto e regexp la funzione

  with temp as ( select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error from dual union all select 109, 'test2', 'Err1' from dual ) SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str FROM (SELECT Name, Project, Error str FROM temp) t CONNECT BY instr(str, ',', 1, level - 1) > 0 order by Name 

FONTE

Senza usare connect by o regexp :

  with mytable as ( select 108 name, 'test' project, 'Err1,Err2,Err3' error from dual union all select 109, 'test2', 'Err1' from dual ) ,x as ( select name ,project ,','||error||',' error from mytable ) ,iter as (SELECT rownum AS pos FROM all_objects ) select x.name,x.project ,SUBSTR(x.error ,INSTR(x.error, ',', 1, iter.pos) + 1 ,INSTR(x.error, ',', 1, iter.pos + 1)-INSTR(x.error, ',', 1, iter.pos)-1 ) error from x, iter where iter.pos < = (LENGTH(x.error) - LENGTH(REPLACE(x.error, ','))) - 1; 

Mi piacerebbe aggiungere un altro metodo. Questo usa querys ricorsive, qualcosa che non ho visto nelle altre risposte. È supportato da Oracle dal 11gR2.

 with cte0 as ( select phone_number x from hr.employees ), cte1(xstr,xrest,xremoved) as ( select x, x, null from cte0 union all select xstr, case when instr(xrest,'.') = 0 then null else substr(xrest,instr(xrest,'.')+1) end, case when instr(xrest,'.') = 0 then xrest else substr(xrest,1,instr(xrest,'.') - 1) end from cte1 where xrest is not null ) select xstr, xremoved from cte1 where xremoved is not null order by xstr 

È abbastanza flessibile con il carattere di divisione. Basta cambiarlo nelle chiamate INSTR .

Ho avuto lo stesso problema e xmltable mi ha aiutato:

 SELECT id, trim(COLUMN_VALUE) text FROM t, xmltable(('"' || REPLACE(text, ',', '","') || '"')) 

Ecco un’implementazione alternativa che utilizza XMLTABLE che consente di eseguire il casting su diversi tipi di dati:

 select xmltab.txt from xmltable( 'for $text in tokenize("a,b,c", ",") return $text' columns txt varchar2(4000) path '.' ) xmltab ; 

… o se le stringhe delimitate sono memorizzate in una o più righe di una tabella:

 select xmltab.txt from ( select 'a;b;c' inpt from dual union all select 'd;e;f' from dual ) base inner join xmltable( 'for $text in tokenize($input, ";") return $text' passing base.inpt as "input" columns txt varchar2(4000) path '.' ) xmltab on 1=1 ; 

A partire da Oracle 12c è ansible utilizzare JSON_TABLE e JSON_ARRAY :

 CREATE TABLE tab(Name, Project, Error) AS SELECT 108,'test' ,'Err1, Err2, Err3' FROM dual UNION SELECT 109,'test2','Err1' FROM dual; 

E interrogare:

 SELECT * FROM tab t OUTER APPLY (SELECT TRIM(p) AS p FROM JSON_TABLE(REPLACE(JSON_ARRAY(t.Error), ',', '","'), '$[*]' COLUMNS (p VARCHAR2(4000) PATH '$'))) s; 

Produzione:

 ┌──────┬─────────┬──────────────────┬──────┐ │ Name │ Project │ Error │ P │ ├──────┼─────────┼──────────────────┼──────┤ │ 108 │ test │ Err1, Err2, Err3 │ Err1 │ │ 108 │ test │ Err1, Err2, Err3 │ Err2 │ │ 108 │ test │ Err1, Err2, Err3 │ Err3 │ │ 109 │ test2 │ Err1 │ Err1 │ └──────┴─────────┴──────────────────┴──────┘ 

avevo usato la funzione DBMS_UTILITY.comma_to _table in realtà il suo funzionamento del codice come segue

 declare l_tablen BINARY_INTEGER; l_tab DBMS_UTILITY.uncl_array; cursor cur is select * from qwer; rec cur%rowtype; begin open cur; loop fetch cur into rec; exit when cur%notfound; DBMS_UTILITY.comma_to_table ( list => rec.val, tablen => l_tablen, tab => l_tab); FOR i IN 1 .. l_tablen LOOP DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i)); END LOOP; end loop; close cur; end; 

avevo usato i miei nomi di tabelle e colonne

 CREATE FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString varchar(1000)) RETURNS @Result TABLE (Column1 VARCHAR(100)) AS BEGIN DECLARE @IntLocation INT WHILE (CHARINDEX(',', @CommadelimitedString, 0) > 0) BEGIN SET @IntLocation = CHARINDEX(',', @CommadelimitedString, 0) INSERT INTO @Result (Column1) --LTRIM and RTRIM to ensure blank spaces are removed SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString, 0, @IntLocation))) SET @CommadelimitedString = STUFF(@CommadelimitedString, 1, @IntLocation, '') END INSERT INTO @Result (Column1) SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed RETURN END GO --Using the UDF to convert comma separated values into rows SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange') SELECT * FROM dbo.BreakStringIntoRows('Apple , Banana, Orange')