sql - separados - separar texto en columnas oracle

Dividir valores separados por comas en Oracle

Question

Tengo una columna en mi base de datos donde los valores vienen de la siguiente manera:

3862,3654,3828

En la columna ficticia cualquier no. de valores separados por comas puede venir. Intenté con la siguiente consulta, pero está creando resultados duplicados.

select regexp_substr(dummy,''[^,]+'',1,Level) as dummycol 
  from (select * from dummy_table) 
 connect by level <= length(REGEXP_REPLACE(dummy,''[^,]+''))+1

No estoy entendiendo el problema. ¿Alguien puede ayudar?

Answer 1

Dando un ejemplo PL / SQL donde se analiza sobre una tabla con un ID y nombre de columna. Esto analizará e imprimirá cada ID y el valor analizado que luego podría insertarse en una nueva tabla o utilizarse de alguna otra manera.

Entrada

Column_ID    Column_Name
123          (3862,3654,3828)

Salida

Column_ID    Column_Name
123          3862
123          3654
123          3828

Código PL / SQL

declare
   table_name1 varchar2(1000);
   string_to_parse varchar2(2000); -- assign string to table name
   string_length   number := 0;    -- string length for loop
   string_value    varchar2(2000); -- string value to store value in
   column_id        number; 
begin
  --some table in the format ''123'' as column_id, ''(3862,3654,3828)'' as column_name

  --remove the parenthesis or other special characters if needed
  update some_table t
     set t.column_name = regexp_replace(t.column_name,''/(|/)'','''');
  commit;

  for i in (
    select * from some_table
    ) loop

    column_id := i.column_id; --assign the id of the colors
    string_to_parse := i.column_name; -- assign string to be parsed

    if string_to_parse is null then
      --at this point insert into a new table, or do whatever else you need
      dbms_output.put_line(column_id || '' '' || string_value);
    else
      --String to parse is the comma
      string_to_parse := string_to_parse||'','';
      string_length   := length(string_to_parse) - length(replace(string_to_parse,'','',''''));

      -- Loop through string from parameter
      for i in 1 .. string_length loop
        -- [^,] matches any character except for the ,
        select regexp_substr(string_to_parse,''[^,]+'',1,i)
        into string_value -- stores value into string_value
        from dual; -- dual is a dummy table to work around

        --at this point insert into a new table, or do whatever else you need
        dbms_output.put_line(column_id || '' '' || string_value);
        --clear out the string value
        string_value := null;
      end loop;
    end if;
  end loop;
end;

Answer 2

Funciona perfectamente para mí -

SQL> WITH dummy_table AS(
  2  SELECT ''3862,3654,3828'' dummy FROM dual
  3  )
  4  SELECT trim(regexp_substr(dummy,''[^,]+'',1,Level)) AS dummycol
  5  FROM dummy_table
  6    CONNECT BY level <= LENGTH(REGEXP_REPLACE(dummy,''[^,]+''))+1
  7  /

DUMMYCOL
--------------
3862
3654
3828

SQL>

Hay muchas otras maneras de lograrlo. Leer División de cadena delimitada por comas simples en filas .

Actualización Respecto a los duplicados cuando se usa la columna en lugar de un único valor de cadena. Vimos el uso de DBMS_RANDOM en la cláusula PRIOR para deshacernos del bucle cíclico aquí

Pruebe lo siguiente,

SQL> WITH dummy_table AS
  2    ( SELECT 1 rn, ''3862,3654,3828'' dummy FROM dual
  3    UNION ALL
  4    SELECT 2, ''1234,5678'' dummy FROM dual
  5    )
  6  SELECT trim(regexp_substr(dummy,''[^,]+'',1,Level)) AS dummycol
  7  FROM dummy_table
  8    CONNECT BY LEVEL          <= LENGTH(REGEXP_REPLACE(dummy,''[^,]+''))+1
  9  AND prior rn                 = rn
 10  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
 11  /

DUMMYCOL
--------------
3862
3654
3828
1234
5678

SQL>

Actualización 2

De otra manera,

SQL> WITH dummy_table AS
  2    ( SELECT 1 rn, ''3862,3654,3828'' dummy FROM dual
  3    UNION ALL
  4    SELECT 2, ''1234,5678,xyz'' dummy FROM dual
  5    )
  6  SELECT trim(regexp_substr(t.dummy, ''[^,]+'', 1, levels.column_value)) AS dummycol
  7  FROM dummy_table t,
  8    TABLE(CAST(MULTISET
  9    (SELECT LEVEL
 10    FROM dual
 11      CONNECT BY LEVEL <= LENGTH (regexp_replace(t.dummy, ''[^,]+'')) + 1
 12    ) AS sys.OdciNumberList)) LEVELS
 13    /

DUMMYCOL
--------------
3862
3654
3828
1234
5678
xyz

6 rows selected.

SQL>