División de cadena en múltiples filas en Oracle

Sé que esto ha sido respondido hasta cierto punto con PHP y MYSQL, pero me preguntaba si alguien podría enseñarme el enfoque más simple para dividir una cadena (delimitada por comas) en múltiples filas en Oracle 10g (preferiblemente) y 11g.

La tabla es la siguiente:

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

Quiero crear lo siguiente:

 Name | Project | Error 108 Test Err 1 108 Test Err 2 108 Test Err 3 109 Test2 Err1 

He visto algunas soluciones potenciales alnetworkingedor de la stack, sin embargo, solo representaban una sola columna (siendo la cadena delimitada por comas). Cualquier ayuda sería muy apreciada.

La respuesta aceptada tiene un performance deficiente cuando se usan grandes sets de datos.

Esta puede ser una forma mejorada (también con regexp y connect by):

 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 

expresiones regulares es una cosa maravillosa 🙂

 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 

Hay una gran diferencia entre los dos siguientes:

  • dividiendo una sola cadena delimitada
  • dividir cadenas delimitadas para múltiples filas en una tabla.

Si no restringe las filas, entonces la cláusula CONNECT BY produciría múltiples filas y no dará el resultado deseado.

  • Para una cadena delimitada individual, mire la cadena delimitada por comas simples en filas
  • Para dividir cadenas delimitadas en una tabla, mire las cadenas delimitadas por comas en una tabla

Además de las expresiones regulares , algunas otras alternativas están utilizando:

  • XMLTable
  • Cláusula MODELO

Preparar

 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> 

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

Usando la cláusula MODELO :

 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 par de más ejemplos de lo mismo:

 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 / 

Además, puede usar DBMS_UTILITY.comma_to_table & table_to_comma: http://www.oracle-base.com/articles/9i/useful-procedures-and-functions-9i.php#DBMS_UTILITY.comma_to_table

Me gustaría proponer un enfoque diferente utilizando una function de tabla PIPELINED. Es algo similar a la técnica de XMLTABLE, excepto que está proporcionando su propia function personalizada para dividir la cadena de caracteres:

 -- 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)); 

Resultados:

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

El problema con este tipo de enfoque es que a menudo el optimizador no conocerá la cardinalidad de la function de tabla y tendrá que adivinar. Esto podría ser potencialmente dañino para sus planes de ejecución, por lo que esta solución se puede ampliar para proporcionar statistics de ejecución para el optimizador.

Puede ver esta estimación del optimizador ejecutando un PLAN EXPLICACIÓN en la consulta anterior:

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

Aunque la colección solo tiene 3 valores, el optimizador estimó 8168 filas para ella (valor pnetworkingeterminado). Esto puede parecer irrelevante al principio, pero puede ser suficiente para que el optimizador decida por un plan subóptimo.

La solución es usar las extensiones del optimizador para proporcionar statistics para la colección:

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

Probando el plan de ejecución resultante:

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

Como puede ver, la cardinalidad en el plan de arriba ya no es el valor calculado 8196. Todavía no es correcto porque estamos pasando una columna en lugar de un literal de cadena a la function.

Sería necesario hacer algunos ajustes al código de la function para dar una estimación más cercana en este caso particular, pero creo que el concepto general se explica bastante aquí.

La function str2tbl utilizada en esta respuesta fue desarrollada originalmente por Tom Kyte: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

El concepto de asociar statistics con types de objects se puede explorar más a background leyendo este artículo: http://www.oracle-developer.net/display.php?id=427

La técnica descrita aquí funciona en 10g +.

Creo que la mejor manera de conectarme y la function de expresión regular

  select regexp_substr(Error ,'[^,]+', 1, level) from your_table connect by regexp_substr(Error , '[^,]+', 1, level) is not null; 

FUENTE

REGEXP_COUNT no se agregó hasta Oracle 11i. Aquí hay una solución de Oracle 10g, adoptada de la solución de 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; 

Sin usar 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; 

Me gustaría agregar otro método. Este usa consultas recursivas, algo que no he visto en las otras respuestas. Es compatible con Oracle desde 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 

Es bastante flexible con el carácter de split. Simplemente INSTR en las llamadas INSTR .

Yo había usado la function DBMS_UTILITY.comma_to _table en realidad está trabajando el código de la siguiente manera

 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; 

había usado mis propios nombres de tabla y columna

Aquí hay una implementación alternativa que utiliza XMLTABLE que permite enviar datos a diferentes types de datos:

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

… o si tus cadenas delimitadas están almacenadas en una o más filas de una tabla:

 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 ; 
 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')