sexta-feira, 10 de fevereiro de 2012

Transformando textos em linhas

Uma função que não se acha no SQL é a split(). Por exemplo, para transformar o string '12,34,56,78,90' em linhas de uma consulta, podemos escrever um pequeno bloco de PL/SQL no Oracle. No entanto, quero usar apenas o SQL.

Minha solução é a seguinte:

select trim(trailing ',' from item) from (
  select regexp_substr('12,34,56,78,90','\d+,',1,n.n) item
  from dual,
    (select rownum n 
     from all_tables 
     where rownum<length(translate('12,34,56,78,90', ',1234567890', ','))+1) n
) 

A função regexp_substr() permite indicar qual ocorrência da busca queremos mostrar, então, junto a tabela dual (que só tem uma linha) com n linhas de all_tables a fim de mostrar todas as ocorrências. Cada uma vira uma linha. Além disso, uso o mesmo string na consulta a all_tables para encontrar o número de vírgulas e somar um, produzindo o número total de elementos na lista.

Para testar mais um pouco minha pequena solução, criei uma tabela dos campões do mundo de futebol:

CREATE TABLE CAMPEOES (
  PAIS VARCHAR2(100), 
  CAMPEONATOS VARCHAR2(200)
)

E dentro dessa tabela coloquei o seguinte:

PAIS CAMPEONATOS
Uruguai 1930,1950
Alemanha 1954,1974,1990
Itália 1934,1938,1982,2006
Argentina 1978,1986
Inglaterra 1966
Espanha 2010
França 1998
Brasil 1958,1962,1970,1994,2002

Para enumerar os campeões ano a ano, executei a seguinte consulta:


select pais, trim(trailing ',' from item) from (
  select pais, regexp_substr(campeonatos||',','\d+,',1,n.n) item
  from campeoes,
    (select rownum n from all_tables where rownum<10) n
) where item is not null
order by 2


Uruguai 1930
Itália 1934
Itália 1938
Uruguai 1950
Alemanha 1954
Brasil 1958
Brasil 1962
Inglaterra 1966
Brasil 1970
Alemanha 1974
Argentina 1978
Itália 1982
Argentina 1986
Alemanha 1990
Brasil 1994
França 1998
Brasil 2002
Itália 2006
Espanha 2010

Modelei ao banco ao problema, mas a solução serve também para quando a modelagem for o problema.

Nenhum comentário: