Crie um site como este com o WordPress.com
Comece agora

Obter o enésimo elemento de uma lista

Sabe aquelas colunas cujo conteúdo é uma lista composta de diversos valores, separados por algum caractere? Algo assim:

'Rua|Santos e Almeida|180||Centro|98900-300|Caripibuna|XX'

E então, como obter o enésimo elemento sem ter que fazer “string split” de todo o conteúdo da coluna?

p017_sumario

1. Introdução

Há alguns dias, em fórum de SQL Server do Microsoft MSDN, um usuário do fórum solicitou auxílio para resolver a seguinte necessidade: “Preciso separar o enésimo campo de uma string com delimitadores”. Como eu sabia que essa necessidade estava relacionada a um código SQL dele com abordagem row by row, sugeri então a utilização de uma função de usuário, do tipo escalar, com uso de loop e funções nativas charindex() e substring() para encontrar o enésimo elemento da lista. Mas parece que ele não gostou muito da solução, pois ele queria algo que não necessitasse de criação de função de usuário. Resolvi então escrever este artigo sobre como obter o enésimo elemento de uma lista com separadores entre valores (ou texto multivalorado).

Há situações em que múltiplos valores estão armazenados em uma coluna, também denominados de texto multivalorado, onde entre cada valor há um separador:

João|Antunes|Pereira

Este separador, geralmente um único caractere mas podendo ser um conjunto de caracteres, deve ser escolhido de modo que se tenha certeza de que ele não faz parte dos caracteres utilizados para a construção dos valores.

Uma abordagem é utilizar técnicas de separação de textos multivalorados (string split) que retornem os valores na posição em que estão na lista; inclusive algumas dessas técnicas estão descritas no artigo “Separar conteúdo de texto multivalorado (string split)”. Uma vez separados os valores, basta então retornar a enésima linha. Outra abordagem é converter a lista para XML ou JSON e então utilizar métodos inerentes a essas construções para obter o n-ésimo elemento. E, é claro, não podemos esquecer da abordagem mais simplista, que é o uso de um par de ponteiros caminhando na lista, até chegar ao enésimo elemento. Esta abordagem envolve o uso da função charindex() dentro de um loop.

Neste artigo estão descritas algumas técnicas de obter o enésimo elemento de uma lista e o encapsulamento dessas técnicas em funções de usuário, tanto do tipo escalar quanto do tipo inline table-valued.

2. Técnicas

Para demonstração das técnicas deste artigo será utilizada a tabela Pessoa, a seguir declarada e carregada com algumas listas.

-- código #2.1
CREATE TABLE Pessoa (
     Nome_completo varchar(80),
     Endereço varchar(100) 
);

INSERT into Pessoa (Nome_completo, Endereço) values
   ('João|Antunes|Pereira', 
    'Rua|Santo Antônio|180||Centro|00183405|Jardim Lusitânia|GH'),
   ('Ana Clara||Macedo', '');

Observe que o conteúdo das colunas está preenchido como uma lista, onde:

p017_item 2 fig 1

sendo que o separador utilizado é o caractere “|”.

2.1. Função parsename()

O objetivo original da função parsename() é separar texto na forma servidor.banco.esquema.objeto em suas partes. O próprio nome da função já indica o objetivo dela. Esta função possui 2 parâmetros e o primeiro (object_name) aceita texto com até 4 partes e no segundo parâmetro (object_piece) qual parte deve ser extraída. O separador das partes deve ser obrigatoriamente o caractere “.” (ponto).

PARSENAME ( 'object_name' , object_piece )

De forma geral, tendo-se texto na forma

parteA.parteB.parteC.parteD

há a seguinte relação entre as partes e os valores de object_piece:

p017_item 2.1 fig 1

Ou seja, object_piece é a numeração sequencial da direita para a esquerda.

A função parsename() pode ser utilizada para facilmente resolver a obtenção do enésimo elemento da coluna, desde que existam no máximo 4 partes. Outro detalhe é que no texto a ser analisado não pode existir o caractere “.”, pois este é o separador padrão utilizado pela função parsename().

-- código #2.2
-- monta variáveis com texto e delimitador
declare @Texto nvarchar(2000), @Separador nchar(1);
set @Texto= N'João|Antunes|Pereira';
set @Separador= N'|';

-- exibe o prenome (primeiro elemento)
SELECT parsename (replace (@Texto, @Separador, '.'), 3);

2.2. Ponteiros com charindex

É uma abordagem simples, em que se utilizam dois ponteiros para caminhar na lista, com um ponteiro indicando o início de um elemento e o segundo ponteiro o final deste elemento. Estes ponteiros caminham na lista até chegarem ou ao enésimo elemento ou ao final da lista. O ponteiro inicial utiliza a função charindex() para saltar de separador em separador.

A função charindex() possui 3 parâmetros:

CHARINDEX ( procurar_por, procurar_em, iniciando_em )

Embora o terceiro parâmetro seja opcional, para este artigo utilizaremos os 3:

    1. procurar_por: o que deve ser procurado; neste artigo, procurar pelo delimitador;
    2. procurar_em: em qual local deve ser realizada a busca;
    3. iniciando_em: a partir de qual posição do texto a busca deve ser iniciada.

Supondo que o objetivo seja obter o primeiro elemento, temos então o seguinte código:

-- código #2.3
-- monta variáveis com texto e delimitador
declare @Texto nvarchar(2000), @Separador nchar(1);
set @Texto= N'João|Antunes|Pereira';
set @Separador= N'|';

-- inicia no primeiro caractere
declare @Pont1 int, @Pont2 int;
set @Pont1= 1;

-- busca o primeiro delimitador
set @Pont2= charindex (@Separador, @Texto, @Pont1);

-- exibe o elemento
SELECT substring (@Texto, @Pont1, (@Pont2 - @Pont1));

Ao executar este código, obtemos o primeiro elemento. É uma situação específica, mas somente para demonstrar a utilização das funções charindex() e substring() na busca e obtenção de determinado elemento da lista.

Para encontrar qualquer elemento utilizando a técnica acima, temos o seguinte código:

-- código #2.4
-- monta variáveis com texto e delimitador
declare @Texto nvarchar(2000), @Separador nchar(1);
set @Texto= N'João|Antunes|Pereira';
set @Separador= N'|';

-- define qual elemento buscar
declare @nElemento int;
set @nElemento= 2;

--
declare @Pont1 int, @Pont2 int, @N int;

set @Texto= @Texto + @Separador;
set @N= 1;
set @Pont2= 0;

while @N <= @nElemento
  begin
  set @Pont1= @Pont2;
  set @Pont2= charindex (@Separador, @Texto, (@Pont1 +1));
  IF @Pont2 = 0 or @Pont2 is null
    break;
  --
  set @N+= 1;
  end;
  
-- exibe o elemento
IF @Pont2 > 0
  SELECT substring (@Texto, (@Pont1 +1), (@Pont2 - @Pont1 -1));

Como são vários elementos, o algoritmo caminha com os ponteiros @Pont1 e @Pont2 ao longo do texto, com ambos delimitando o elemento. Ao atingir o enésimo elemento, o loop é interrompido e o valor do elemento é exibido.

2.3. Objeto JSON

Para manipular objetos json (JavaScript Object Notation) no SQL Server existem algumas funções nativas, dentre as quais a json_value(), disponível a partir da versão 2016 do SQL Server. Esta função extrai um valor escalar de uma cadeia de caracteres json.

JSON_VALUE ( expressão JSON [ , caminho ] )

O que temos que fazer é transformar a lista em um objeto json, do tipo array, transformando, por exemplo,

João|Antunes|Pereira

em

{"vet":["João","Antunes","Pereira"]}

e então submetendo esse objeto à função json_value().

-- código #2.5
-- monta variáveis com texto e delimitador
declare @Texto nvarchar(2000), @Separador nchar(1);
set @Texto= N'João|Antunes|Pereira';
set @Separador= N'|';

-- define qual elemento buscar
declare @nElemento int;
set @nElemento= 2;

-- converte o texto
declare @TextoJSON nvarchar(2000);
set @TextoJSON= N'{"vet":["' 
                  + replace (@Texto, @Separador, '","') 
                  + N'"]}';

-- obtém o elemento
SELECT json_value(@TextoJSON, 
                  '$.vet['+cast((@nElemento -1) as varchar)+']');

A indexação do array inicia em 0; por isso que no código é pesquisado pelo elemento de número (@nElemento -1).

2.4. Objeto XML

Às vezes eu acho que XML é o ppto. Nesta solução, o que é feito é a transformação da lista em um objeto xml, ao envolver cada valor em tags <x> e </x>.

-- código #2.6
-- monta variáveis com texto e delimitador
declare @Texto nvarchar(2000), @Separador nchar(1);
set @Texto= N'João|Antunes|Pereira';
set @Separador= N'|';

-- define qual elemento buscar
declare @nElemento int;
set @nElemento= 2;

-- converte o texto
declare @TextoXML xml;
set @TextoXML= cast (N'<x>' 
                   + replace (@Texto, @Separador, N'</x><x>') 
                   + N'</x>' as XML);

-- obtém o elemento
SELECT @TextoXML.value('/x[sql:variable("@nElemento")][1]',
                       'nvarchar(2000)');

No código acima, a lista, originalmente com o valor

João|Antunes|Pereira

é transformada em

<x>João</x><x>Antunes</x><x>Pereira</x>

Um truque simples mas o suficiente para converter a lista em um objeto XML e então utilizar o método value para obter o valor do elemento. A solução com XML foi baseada em resposta de “Shnugo” no tópico “Using T-SQL, return nth delimited element from a string” (vide capítulo Referências, ao final).

2.5. Split string ordenado

No artigo “Separar conteúdo de texto multivalorado (string split)” estão descritas algumas técnicas para separar um texto multivalorado (uma lista de campos com separadores entre campos). Algumas daquelas técnicas podem ser utilizadas, primeiro separando os elementos em n linhas e então retornando a enésima linha. Entretanto, a impressão inicial é que esta abordagem causa processamento adicional desnecessário. De qualquer forma fica registrada aqui a observação, para quem se interesse em avaliar esta abordagem.

3. Funções n_elemento()

Cada uma das técnicas tratadas no capítulo anterior exige alguma transformação de modo a ajustar o conteúdo da lista às características da técnica. Além disso, imagina copiar o código da técnica toda vez que for necessário obter determinado elemento de uma lista; pode não ser muito prático. Mas e se transformamos cada uma das técnicas em uma função de usuário, com somente 3 parâmetros (lista, separador, posição do elemento a retornar), sem necessidade de qualquer tratamento prévio?

Escalar x inline table-valued. O SQL Server permite a criação de funções de usuários de 3 tipos; neste artigo nos interessam dois tipos: escalar e inline table-valued (iTVF). As funções do tipo escalar possuem programação mais simples e também são mais simples de serem utilizadas; elas retornam um único valor, a cada chamada. A desvantagem é a performance, para casos em que haja elevado número de linhas a processar.

Eis um exemplo de como ficaria a chamada de uma função do tipo escalar:

-- código #3.1
SELECT P.Nome_completo,
       dbo.n_elemento (P.Nome_completo, '|', 1) as Prenome
   from Pessoa as P;

Simples de utilizar, não?

Já as funções de usuário do tipo inline table-valued têm como característica principal que o resultado é uma tabela, podendo inclusive ter várias colunas. Elas permitem construções set-based. A desvantagem é a forma um pouco diferente de serem utilizadas, pois ficam normalmente na cláusula FROM (afinal, retornam uma tabela!).

-- código #3.2
SELECT P.Nome_completo, T.Elemento as Prenome
  from Pessoa as P
       outer apply dbo.n_elemento_itv (P.Nome_completo, '|', 1) as T;

Observe que no exemplo acima a função está na cláusula FROM e a junção dela com a tabela principal foi através do operador APPLY.

Neste capítulo cada uma das técnicas abordadas no capítulo anterior será encapsulada em função de usuário com o nome n_elemento_ssss, onde ssss é uma abreviatura da técnica. Dentro do possível, serão apresentadas versões dos tipos escalar e inline table-valued para cada técnica.

3.1. n_elemento_prse()

A primeira técnica a ser encapsulada é a que utiliza a função nativa parsename().

3.1.1. Escalar

A função nativa parsename() foi encapsulada na função n_elemento_prse. Internamente os 3 parâmetros são analisados, inclusive se há a presença do caractere “.” na lista, o que geraria retorno de resultado errado.

-- código #3.3
CREATE FUNCTION dbo.n_elemento_prse (
      @pTexto nvarchar(2000),
      @pSeparador nchar(1),
      @pElemento smallint
)
returns nvarchar(2000)

begin
-- validação dos parâmetros
IF @pTexto is null
   or len (@pTexto) = 0
   or charindex ('.', @pTexto) > 0
   or @pSeparador is null
   or len (@pSeparador) = 0
   or @pElemento is null
   or @pElemento <= 0      
  return NULL;  

-- validação de separadores
declare @QtdSep int;
set @QtdSep= len (@pTexto) - len (replace (@pTexto, @pSeparador, ''));
IF @QtdSep > 3
  return NULL;

-- obtém o elemento
declare @Elemento nvarchar(2000);
SELECT @Elemento= parsename (replace (@pTexto, @pSeparador, '.'), 
                             @QtdSep + 2 - @pElemento);
IF @@rowcount = 0
  return NULL;

--
return @Elemento;
end;
go

Observe que na validação de parâmetros foi analisado o conteúdo do parâmetro @pTexto, para ter certeza de que nele não está presente o caractere “.”. Outra análise efetuada é a verificação se há no máximo 3 separadores, pois a função parsename() aceita no máximo 4 elementos. Estando corretos os parâmetros, a função parsename() é chamada e o elemento obtido é retornado.

3.1.2. iTVF

Como mencionado no início deste capítulo, a implementação de funções do tipo inline table-valued é um pouco mais complexa, pois ela deve ter um único comando SELECT. Então, toda a parte de validação dos parâmetros deve ficar no comando SELECT, na cláusula WHERE.

-- código #3.4
CREATE FUNCTION dbo.n_elemento_prse_itv (
     @pTexto nvarchar(2000),
     @pSeparador nchar(1),
     @pElemento smallint
)
returns table
return (
with Formatado as (
SELECT replace (@pTexto, @pSeparador, '.') as Texto, 
       @pElemento as nElemento,
       (len (@pTexto) - len (replace (@pTexto, @pSeparador, ''))) as QtdSep
) 
SELECT cast (parsename (Texto, (QtdSep + 2 - nElemento))
             as nvarchar(2000)) as Elemento
  from Formatado
  where @pTexto is not null
        and len (@pTexto) >= 0
        and charindex ('.', @pTexto) = 0
        and @pSeparador is not null
        and QtdSep <= 3
        and @pElemento is not null
        and @pElemento > 0
); 
go

Para facilitar a conversão de escalar para inline table-valued foi utilizada CTE para calcular a quantidade de separadores, facilitando assim a codificação e manutenção do código. Aliás, recomendo a leitura do artigo “Programação modular com expressões de tabela (CTE)”, que traz explicações sobre as vantagens no uso de CTE na construção de códigos modulares.

Compare o código #3.4 com o código #3.3; observe com atenção as diferentes formas de construção de código SQL para obter o mesmo objetivo. O que se perdeu ao converter a função do tipo escalar para inline table-valued? A legibilidade do código; fica mais difícil de compreender como ele funciona.

3.2. n_elemento_pont()

A segunda técnica a ser encapsulada é a de ponteiros com charindex, detalhada no item 2.2.

3.2.1. Escalar

A técnica de ponteiros com charindex() foi encapsulada na função n_elemento_pont. Os 3 parâmetros são analisados, de modo a evitar que algum parâmetro incorreto acarrete em erro de execução na função.

-- código #3.5
CREATE FUNCTION dbo.n_elemento_pont (
      @pTexto nvarchar(2000),
      @pSeparador nchar(1),
      @pElemento smallint
)
returns nvarchar(2000)
begin
-- validação dos parâmetros
IF @pTexto is null
   or len (@pTexto) = 0
   or @pSeparador is null
   or len (@pSeparador) = 0
   or @pElemento is null
   or @pElemento <= 0
  return NULL;
     
--
declare @Texto nvarchar(2000), @N int, @Pont1 int, @Pont2 int;
set @Texto= @pTexto + @pSeparador;

set @N= 1;
set @Pont2= 0;
while @N <= @pElemento
  begin
  set @Pont1= @Pont2;
  set @Pont2= charindex (@pSeparador, @Texto, (@Pont1 +1));
  IF @Pont2 = 0 or @Pont2 is null
    break;
  --
  set @N+= 1;
  end;

--
IF @Pont2 = 0 or @Pont2 is null
  return NULL;

--
return substring (@Texto, (@Pont1 +1), (@Pont2 - @Pont1 -1));
end;
go

3.2.2. iTVF

Implementar a técnica ponteiros com charindex exige um pouco mais de artifícios, para conseguir que tudo seja resolvido em uma única instrução SELECT.

3.3. n_elemento_json()

3.3.1. Escalar

A função nativa json_value() foi encapsulada na função n_elemento_json. Da mesma forma que nas funções anteriores, o primeiro passo é validar os parâmetros. Estando corretos, o texto da lista é convertido para objeto json e então enviado para a função json_value(), que retorna o enésimo elemento.

-- código #3.7
CREATE FUNCTION dbo.n_elemento_json (
      @pTexto nvarchar(2000),
      @pSeparador nchar(1),
      @pElemento smallint
)
returns nvarchar(2000)

begin
-- validação dos parâmetros
IF @pTexto is null
   or len (@pTexto) = 0
   or @pSeparador is null
   or len (@pSeparador) = 0
   or @pElemento is null
   or @pElemento <= 0
  return NULL;

-- converte o texto
declare @TextoJSON nvarchar(2000);
set @TextoJSON= N'{"vet":["' 
                  + replace (@pTexto, @pSeparador, '","') 
                  + N'"]}';

-- obtém o elemento
return json_value(@TextoJSON, 
                  '$.vet['+cast((@pElemento -1) as varchar)+']');

end;
go

Observe que a numeração de elementos no array do objeto json inicia em 0 (zero), o que explica o ajuste no valor do parâmetro @pElemento.

3.3.2. iTVF

Como mencionado no início deste capítulo, a implementação de funções do tipo inline table-valued é um pouco mais complexa, pois ela deve ter um único comando SELECT. Então, toda a parte de validação dos parâmetros deve ficar no comando SELECT, na cláusula WHERE.

-- código #3.8
CREATE FUNCTION dbo.n_elemento_json_itv (
     @pTexto nvarchar(2000),
     @pSeparador nchar(1),
     @pElemento smallint
)
returns table
return (
SELECT cast (
       json_value (N'{"vet":["' 
                     + replace (@pTexto, @pSeparador, '","') 
                     + N'"]}',
                   '$.vet['+cast ((@pElemento -1) as varchar)+']') 
       as nvarchar(2000)) as Elemento
  where @pTexto is not null
        and len (@pTexto) >= 0
        and @pSeparador is not null
        and @pElemento is not null
        and @pElemento > 0
); 
go

Nesta implementação não foi utilizada CTE, mas poderia ter sido; por exemplo, para conversão do texto da lista em um objeto json. Fica como demonstração de uma implementação direta e o grau maior de dificuldade na criação e manutenção do código. Observe que a instrução SELECT não possui cláusula FROM.

Compare o código #3.7 com o código #3.8; observe com atenção as diferentes formas de construção de código SQL para obter o mesmo objetivo.

3.4. n_elemento_xml()

A última técnica a ser encapsulada é a de transformação da lista em objeto xml, detalhada no item 2.4.

3.4.1. Escalar

A técnica de objeto xml foi encapsulada na função n_elemento_xml. Da mesma forma que nas funções anteriores, o primeiro passo é validar os parâmetros. Estando corretos, o texto da lista é convertido para objeto xml e então extraído o enésimo elemento.

-- código #3.9
CREATE FUNCTION dbo.n_elemento_xml (
     @pTexto nvarchar(2000),
     @pSeparador nchar(1),
     @pElemento smallint
)
returns nvarchar(2000)

begin
-- validação dos parâmetros
IF @pTexto is null
   or len (@pTexto) = 0
   or charindex (@pTexto, '') > 0
   or @pSeparador is null
   or len(@pSeparador) = 0
   or @pElemento is null
   or @pElemento <= 0
  return NULL;

-- converte o texto
declare @TextoXML xml;
set @TextoXML= cast (N'<x>' 
                     + replace (@pTexto, @pSeparador, N'</x><x>') 
                     + N'</x>' as XML);

-- obtém o elemento
return @TextoXML.value('/x[sql:variable("@pElemento")][1]',
                       'nvarchar(2000)');
end;
go

3.4.2. iTVF

Como mencionado no início deste capítulo, a implementação de funções do tipo inline table-valued é um pouco mais complexa, pois ela deve ter um único comando SELECT. Então, toda a parte de validação dos parâmetros deve ficar no comando SELECT, na cláusula WHERE.

-- código #3.10
CREATE FUNCTION dbo.n_elemento_xml_itv (
     @pTexto nvarchar(2000),
     @pSeparador nchar(1),
     @pElemento smallint
)
returns table
return (
with Formatado as (
SELECT cast (N'<x>'
             + replace (@pTexto, @pSeparador, N'</x><x>')
             + N'</x>' as XML) as TextoXML
) 
SELECT TextoXML.value('/x[sql:variable("@pElemento")][1]',
                      'nvarchar(2000)') as Elemento
  from Formatado
  where @pTexto is not null
        and len (@pTexto) >= 0
        and charindex ('', @pTexto) = 0
        and @pSeparador is not null
        and @pElemento is not null
        and @pElemento > 0
); 
go

Para facilitar a conversão de escalar para inline table-valued foi utilizada CTE para transformar o texto da lista em uma construção XML.

Compare o código #3.9 com o código #3.10; observe com atenção as diferentes formas de construção de código SQL para obter o mesmo objetivo.

4. Performance

Uma dúvida que pode ter surgido em você é sobre a performance dentre as funções definidas neste capítulo: qual delas é mais eficiente e em quais condições?

Vamos analisar os planos de execução de cada técnica? Para quem não tem hábito de analisar planos de execução, ou mesmo nem os conhece, sugiro a leitura do artigo “O Plano Perfeito”. É um texto introdutório ao assunto e traz, ao final, ótimas indicações de livros (gratuitos no formato e-book) e vídeos.

5. Referências

5.1. Documentação SQL Server

5.2. Tópicos de fóruns

5.3. Artigos

PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Obter o enésimo elemento de uma lista

Publicidade

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

%d blogueiros gostam disto: