sql - separar - oráculo: divide varios valores separados por comas en la tabla de oráculo en varias filas

separar cadena oracle

Question

Tengo un problema con la consulta dividida de Oracle.

Al dividir los datos separados por comas en varias filas usando connect by y la expresión regular en la consulta de Oracle, obtengo más filas duplicadas . por ejemplo, en realidad, mi tabla tiene 150 filas en esa dos filas que tienen cadenas separadas por comas, así que en general tengo que obtener solo 155 filas pero obtengo 2000 filas. Si utilizo distinct funciona bien, pero no quiero filas duplicadas en el resultado de la consulta.

Intenté la siguiente consulta, pero está generando filas duplicadas en el resultado de la consulta:

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

EDITAR

La consulta de selección anterior solo puede dividir una sola cadena delimitada por comas , sin embargo, produce filas duplicadas cuando se ejecuta en una tabla con varias filas . ¿Cómo restringir las filas duplicadas?

Answer 1

Prueba así,

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 2

Puede usar la consulta siguiente para convertir valores separados por comas en filas

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

Answer 3

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

Answer 4

La respuesta aceptada usa la condición DBMS_RANDOM.VALUE IS NOT NULL que es inapropiada. Simplemente evita el ciclo cíclico, sin embargo, surgiría una pregunta directa como ¿Cómo y cuándo dbms_random.VALUE puede ser nulo? Lógicamente, nunca será NULL .

La solución más adecuada es usar sys.odciNumberList y evitar el ciclo cíclico.

Por ejemplo,

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>

Consulta obligatoria:

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 solución alternativa 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>

Hay muchas maneras de lograr la tarea, como una cláusula MODELO . Para ver más ejemplos, vea Separar cadenas delimitadas por comas en una tabla

Answer 5

Finalmente, se me ocurrió esta respuesta

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 6

Agregar una cláusula única hace el truco:

 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;