Separando

Como transformar uma string delimitada em linhas no Oracle?

Boa tarde !

Hoje vou escrever sobre um assunto que me foi questionado nos comentários de um post anterior. No outro post, eu mostrei algumas maneiras de transformar várias linhas de uma tabela em apenas uma linha via select, com valores separados por um delimitador. Após algum tempo da publicação, recebi um comentário de um leitor perguntando como seria possível fazer o inverso, ou seja, transformar uma string de uma linha só, com vários valores separados também por um delimitador em várias linhas.

Para resolver a questão pensei em duas formas:

  1. Utilizando uma função PIPELINED

    Essa solução tem a lógica mais simples, pois é criada uma função em PL/SQL onde há um loop que percorre a string inteira a procura de ocorrências do delimitador. Essa função deve ser PIPELINED, ou seja, retorna uma espécie de “tabela em memória”.
    Segue o código da package com a função:
    CREATE OR REPLACE PACKAGE PKG_SEPARA AS
      TYPE linhas IS TABLE OF VARCHAR2(32000);
      FUNCTION explodir(p_string IN CLOB, p_delimiter IN VARCHAR2) RETURN linhas PIPELINED;
    END pkg_separa;
    /
    
    CREATE OR REPLACE PACKAGE BODY PKG_SEPARA AS
      FUNCTION explodir(p_string IN CLOB, p_delimiter IN VARCHAR2) RETURN linhas PIPELINED AS
        v_linha varchar2(32000);
        v_cont  number := 1;
        v_ate   number;
      BEGIN
        loop
          v_ate := instr(substr(p_string,v_cont), p_delimiter);
          if (v_ate = 0 and v_cont < length(p_string)) then
            v_ate := length(p_string);
          end if;
          v_linha := substr(substr(p_string,v_cont), 1, v_ate - 1);
          v_cont := v_cont + v_ate;
          exit when trim(v_linha) is null;
          pipe row (v_linha);
        end loop;
      END explodir;
    END PKG_SEPARA;
    /
    
    E para testarmos:
    select *
      from table(pkg_separa.explodir('1245;45678;7789;64646;4478',';'));
  2. Utilizando uma query recursiva

    Se você não puder ou não quiser criar objetos no banco de dados, é possível fazer com apenas uma query recursiva, utilizando a palavra chave connect e brincando com os levels. Segue o código:

    with str as (select '1245;45678;7789;64646;4478' x, ';' delimiter from dual)
    select substr(str.x
                 ,instr(str.delimiter||ltrim(str.x,str.delimiter),str.delimiter,1,level)
                 ,instr(rtrim(str.x,str.delimiter)||str.delimiter,str.delimiter,1,level)
                 -instr(str.delimiter||ltrim(str.x,str.delimiter),str.delimiter,1,level)) a
    from str
    connect by level <= regexp_count(str.x, str.delimiter) + 1;

    [UPDATE] O leitor Márcio Faria sugeriu um outro exemplo bem bacana e muito mais limpo. Segue o código:

    SELECT regexp_substr(info, separador, 1, LEVEL) info
      FROM (SELECT '1245;45678;7789;64646;4478' info, '[^;]+' separador FROM dual)
    CONNECT BY regexp_substr(info, separador, 1, LEVEL) IS NOT NULL

Bom, é isso. Testei os códigos acima com o Oracle 11g, tanto com strings varchar2 ou clob.

Até mais!

No inferno, os lugares mais quentes são reservados àqueles que escolheram a neutralidade em tempo de crise.
– Dante Alighieri

6 opiniões sobre “Como transformar uma string delimitada em linhas no Oracle?”

  1. Segue uma variação do primeiro exemplo, dessa vez exibindo os valores em colunas…
    O símbolo | (pipe) serve para separar em linhas e o símbolo ; (ponto e vírgula) separa as colunas. Dessa forma, obtém-se o resultado de um String em duas colunas.

    SELECT regexp_substr(regexp_substr(info, separador, 1, LEVEL),
    ‘[^;-]+’,
    1,
    1) idfruta,
    regexp_substr(regexp_substr(info, separador, 1, LEVEL),
    ‘[^;-]+’,
    1,
    2) nmfruta
    FROM (SELECT ‘1;Maçã|2;Banana|3;Laranja’ info, ‘[^|]+’ separador FROM dual)
    CONNECT BY regexp_substr(info, separador, 1, LEVEL) IS NOT NULL

    Abs,
    Marcio Faria

  2. Bom dia Vinícius,

    você poderia me explicar como fazer na query abaixo, fazer para retornar os valores sem duplicação?
    Pegando seu exemplo alterei e adicionei uma query com union all para simular 2 linhas no resultado. Gostaria que retornasse:
    111
    222
    333
    444

    SELECT regexp_substr(info, separador, 1, LEVEL) info
    FROM (SELECT ‘111;222;’ info, ‘[^;]+’ separador FROM dual
    UNION ALL
    SELECT ‘333;444’ info, ‘[^;]+’ separador FROM dual
    )
    CONNECT BY regexp_substr(info, separador, 1, LEVEL) IS NOT NULL

    1. Rodrigo,

      Acho que a maneira mais simples é adicionar um distinct na query:

      SELECT distinct regexp_substr(info, separador, 1, LEVEL) info
      FROM (SELECT ‘111;222;’ info, ‘[^;]+’ separador FROM dual
      UNION ALL
      SELECT ‘333;444′ info, ‘[^;]+’ separador FROM dual
      )
      CONNECT BY regexp_substr(info, separador, 1, LEVEL) IS NOT NULL

      1. O problema é que pode repetir, pois preciso fazer um agrupamento para contagem como abaixo… e teria que vir assim:
        info qtde
        111 2
        222 1
        333 1
        444 1

        SELECT info, Count(*) qtde FROM
        (
        SELECT regexp_substr(info, separador, 1, LEVEL) info
        FROM (SELECT ‘111;222;’ info, ‘[^;]+’ separador FROM dual
        UNION ALL
        SELECT ‘333;444;111’ info, ‘[^;]+’ separador FROM dual
        )
        CONNECT BY regexp_substr(info, separador, 1, LEVEL) IS NOT NULL
        ) GROUP BY info

        1. Rodrigo,

          Parei pra analisar melhor, e não vai dar certo mesmo, as soluções apresentadas são para apenas uma linha.
          Uma opção seria transformar as duas (ou mais) linhas do union em apenas uma, e então aplicar o método acima.
          Concordo que não é uma solução bonita, mas funcionaria.
          No blog falo em um outro post sobre como unificar linhas de um select, talvez ajude.

          Abs.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *