Como Remover o Delimitador de uma String de Dados

Imagine o seguinte problema: Dada uma lista de códigos de empregados (‘PMA42628M,L-B31947F,PTC11962M,PHF38899M,M-L67958F,LAL21447M’), onde cada código está delimitado por uma vírgula, você precisa remover o delimitador e retornar todos os registros da tabela de empregados cujo código está presente na lista.

Bom, antes de mais nada quero dizer que existem várias soluções para este problema mas nesta dica estarei demonstrando como fazer isso utilizando-se uma função.

A FUNÇÂO
========
A função é bastante simples, dada uma lista de valores delimitados por vírgula (,) ela varre a lista, localiza e remove o delimitador, e armazena os códigos dos empregados em uma variável do tipo TABLE.

Duas grandes vantagens de se armazenar os valores em uma variável do tipo TABLE são:

1. Caso a lista seja muito grande é possível criar uma chave primaría, o que melhorará a performance da consulta,
2. Para filtrar os registros, pode-se utilizar um INNER JOIN entre a tabela principal e a tabela retornada pela função.
3. Para inserir os valores em uma nova tabela basta executar um INSERT INTO com SELECT.

Como podemos notar no corpo da função, três funções de sistema bastante interessantes são utilizadas aqui:

COALESCE: A função de sistema COALESCE retorna o primeiro valor não nulo entre os seus argumentos. Neste caso, quando a variável @CodList for NULL, @CodList2 será igual a ”.

SET @CodList2 = COALESCE(@CodList, ”)

PATINDEX: A função de sistema PATINDEX retorna a posição inicial de um dado caracter dentro de uma string. Neste caso, quando o delimitador não estiver mais presente em @CodList2, saímos do Loop.

WHILE PATINDEX(‘%,%’ , @CodList2 ) <> 0

SELECT @PosicaoDelimitador = WHILE PATINDEX(‘%,%’, @CodList2)

STUFF: A função de sistema STUFF, exclui um tamanho especificado de caracter e insere outro conjunto de caracter em seu lugar. Neste caso, utilizamos STUFF para substituir o código já capturado por ”.

SELECT @CodList2 = STUFF(@CodList2, 1, @PosicaoDelimitador, ”)

Sendo assim, para um @CodList2 como ‘PMA42628M,L-B31947F,PTC11962M’, após o STUFF, @CodList2 será igual a ‘L-B31947F,PTC11962M’.

— Código para criar a função
CREATE FUNCTION dbo.fn_RemoveDelimitador
(
   @CodList varchar(8000)
)
— Cria variável do tipo TABLE
RETURNS @TBCODID TABLE (CodID char(9) NOT NULL PRIMARY KEY)
AS
BEGIN
    DECLARE @PosicaoDelimitador int
    DECLARE @CodList2 varchar(8000)
    DECLARE @Codigo varchar(8000)
    — Se @CodList for NULL, então @CodList será igual a ”
    SET @CodList2 = COALESCE(@CodList, ”)
    IF @CodList2 <> ”
    BEGIN
    — Adiciona um delimitador no final da lista
    SET @CodList2 = @CodList2 + ‘,’
    — Enquanto houver uma vírgula na lista de código, entra no Loop
    WHILE PATINDEX(‘%,%’ , @CodList2 ) <> 0
    BEGIN
        — Pega a posição do delimitador dentro da lista
        SELECT @PosicaoDelimitador = PATINDEX(‘%,%’ , @CodList2)
        — Pega o próximo código
        SELECT @Codigo = LEFT(@CodList2, @PosicaoDelimitador – 1)
        — Insere o código na tabela variável
        INSERT INTO @TBCODID (CodID) VALUES (CONVERT(char(9), @Codigo))
        — Remove o código da lista
        SELECT @CodList2 = STUFF(@CodList2, 1, @PosicaoDelimitador, ”)
    END
    END
RETURN
END

USANDO A FUNÇÂO
==============
Uma vez criada a função, ao executar a query abaixo passando como parâmetro a lista de códigos, teremos o seguinte resultado:

DECLARE @ListadeCodigo varchar(8000)
SET @ListadeCodigo = ‘PMA42628M,L-B31947F,PTC11962M,PHF38899M,M-L67958F,LAL21447M’
SELECT * from dbo.fn_RemoveDelimitador(@ListadeCodigo)

Resultado:
———-
CodID
———
L-B31947F
LAL21447M
M-L67958F
PHF38899M
PMA42628M
PTC11962M

Agora imagine que você possui uma tabela e precisa inserir os códigos retornados sobre uma outra tabela em seu banco de dados. Bom, neste caso basta você executar um INSERT INTO como no exemplo abaixo. Neste exemplo o resultado do SELECT é inserido sobre a coluna Codigo da tabela TB_TESTE.

CREATE TABLE TB_TESTE(id int identity, Codigo varchar(20))DECLARE @ListadeCodigo varchar(8000)
SET @ListadeCodigo = ‘PMA42628M,L-B31947F,PTC11962M,PHF38899M,M-L67958F,LAL21447M’

INSERT INTO TB_TESTE (Codigo)SELECT * from dbo.fn_RemoveDelimitador(@ListadeCodigo)

Para este exemplo o resultado é como abaixo:

id             Codigo
———– ——————–
1              L-B31947F
2              LAL21447M
3              M-L67958F
4              PHF38899M
5              PMA42628M
6              PTC11962M

Como nosso objetivo é retornar todos os registros da tabela pubs..Employee cujo emp_id está presente na lista acima, se fizermos um INNER JOIN entre a tabela pubs..Employee e o resultado retornado pela função, teremos então os registros desejados.

DECLARE @ListadeCodigo varchar(8000)
SET @ListadeCodigo = ‘PMA42628M,L-B31947F,PTC11962M,PHF38899M,M-L67958F,LAL21447M’

SELECT emp.emp_id, emp.fname,emp.lname FROM pubs..Employee emp INNER JOIN dbo.fn_RemoveDelimitador(@ListadeCodigo) ResultFuncao ON (emp.emp_id = ResultFuncao.CodID)

Resultado
————–
emp_id         fname                lname
———         ——————– ——–
L-B31947F    Lesley               Brown
LAL21447M   Laurence             Lebihan
M-L67958F    Maria                Larsson
PHF38899M   Peter                Franken
PMA42628M  Paolo                Accorti
PTC11962M  Philip               Cramer

É isso aí pessoal, agora usem a imaginação para adaptar esta função às suas necessidades.

Esta função assume que o delimitador é uma vírgula (,), no entando, é possível alterá-la para trabalhar com qualquer outro delimitador. Também assume-se que os códigos dos empregados é do tipo char (o mesmo tipo da coluna emp_id da tabela empregados), mas também é possível alterá-la para trabalhar com qualquer outro tipo de dados.

Avaliação: