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;