Tomei conhecimento de solicitação em como obter o nome de pessoa que está em texto contendo diversas informações. Nesse texto não há delimitador padrão, pois é um texto sem especificação de campos e sem delimitadores entre as informações, mas o autor da solicitação percebeu um padrão na região que delimita o nome de pessoa:
- antes do nome de pessoa existe o texto “: ”
- após o nome de pessoa existe a data 12/11/2020.
Essas duas ocorrências únicas podem atuar como delimitadores do nome de pessoa, permitindo o uso da função CHARINDEX para obter a posição do primeiro delimitador (“: ”) e, a seguir, do segundo delimitador.
Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.
Abordagem tradicional
O primeiro passo é obter a posição imediatamente após o primeiro delimitador, usando para tal a função CHARINDEX:
-- código #1
-- © José Diz / Porto SQL
declare @Texto varchar(300);
set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais';
-- posição inicial
SELECT @Texto,
(charindex (': ', @Texto) +2) as Pos1;
O valor de Pos1 é 48, que corresponde à posição inicial do nome.
O passo seguinte é obter a posição do segundo delimitador, que podemos considerar como “ 12/”. Utilizamos novamente a função CHARINDEX, mas agora iniciando a pesquisando a partir da posição inicial do nome.
-- código #2
-- © José Diz / Porto SQL
declare @Texto varchar(300);
set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais';
-- texto restante
SELECT @Texto as Texto,
(charindex (': ', @Texto) +2) as Pos1,
charindex (' 12/', @Texto, (charindex (': ', @Texto) +2)) as Pos2;
Obtemos agora os valores 48 e 69. Ao subtrair 48 de 69 temos 21, que é o tamanho exato do nome de pessoa: “ALESSA ANDRO TEIXEIRA”. Agora que temos a posição inicial do nome de pessoa e também o tamanho, podemos utilizar a função SUBSTRING para obter o nome de pessoa:
-- código #3 -- Autor: José Diz -- Publicado em: Porto SQL - https://portosql.wordpress.com declare @Texto varchar(300); set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais'; -- texto restante SELECT @Texto as Texto, substring (@Texto, (charindex (': ', @Texto) +2), (charindex (' 12/', @Texto, (charindex (': ', @Texto) +2)) - (charindex (': ', @Texto) +2)) ) as [Nome de pessoa];
O código sql anterior extrai corretamente o nome, considerando-se a existência dos separadores informados. Entretanto, o código sql me parece confuso, principalmente no cálculo do tamanho do texto a ser extraído; são tantos parênteses, charindex dentro de charindex que quem lê o código sql pela primeira vez pode ter dificuldade na compreensão de como ele funciona. Aliás, mesmo quem escreveu o código sql.
Outro detalhe com relação ao código #3, e anteriores, é que eles consideram que sempre existirão os delimitadores “: ” e “ 12/” no texto a ser pesquisado. Se não houver o resultado é imprevisível, podendo até mesmo ocorrer o seguinte erro na execução:
Invalid length parameter passed to the LEFT or SUBSTRING function
Para tratar a possibilidade de não existir delimitador no texto, o código sql ficaria ainda mais confuso:
-- código #4 -- Autor: José Diz -- Publicado em: Porto SQL - https://portosql.wordpress.com declare @Texto varchar(300); set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais'; -- texto restante SELECT @Texto as Texto, case when charindex (': ', @Texto) > 0 then case when charindex (' 12/', @Texto, (charindex (': ', @Texto) +2)) > 0 then substring (@Texto, (charindex (': ', @Texto) +2), (charindex (' 12/', @Texto, (charindex (': ', @Texto) +2)) - (charindex (': ', @Texto) +2))) else NULL end else NULL end as [Nome de pessoa];
Aliás, tão confuso que tive alguma dificuldade em construí-lo: utilizando construções CASE primeiro é verificado se existe o delimitador inicial; a seguir é verificado se existe o delimitador final, após a ocorrência do primeiro delimitador; somente então a função SUBSTRING é utilizada. Caso um dos delimitadores não seja encontrado o código #4 retorna NULL.
Mas há alguma forma de tornar a programação mais simples e que facilite a posterior manutenção, inclusive por terceiros que jamais tenham visto o código sql?
CTE para tornar modular a codificação
Para tornar o código sql mais legível podemos utilizar o artifício de CTE encadeadas como descrito no artigo “Programação modular com expressões de tabela (CTE)”.
-- código #5 -- Autor: José Diz -- Publicado em: Porto SQL - https://portosql.wordpress.com declare @Texto varchar(300); set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais'; with Passo1 as ( SELECT @Texto as Texto, (charindex (': ', @Texto) +2) as Pos1 ), Passo2 as ( SELECT Texto, Pos1, charindex (' 12/', Texto, Pos1) as Pos2 from Passo1 ) SELECT Texto, Pos1, Pos2, substring (Texto, Pos1, (Pos2 - Pos1)) as [Nome de pessoa] from Passo2;
Na CTE Passo1 é calculada a posição inicial do nome de pessoa, armazenada no alias Pos1. A seguir, a CTE Passo2 calcula a posição final do nome de pessoa, utilizando Pos1 como posição inicial de pesquisa. E, finalizando, a função SUBSTRING utiliza os aliases Pos1 e Pos2 para obter o nome de pessoa. Bem mais simples de construir, fácil de entender e de dar manutenção.
Mas é necessário alterar o código #5 de modo que ele também trate ausência de delimitadores, como foi feito entre os códigos #3 e #4.
-- código #6 -- Autor: José Diz -- Publicado em: Porto SQL - https://portosql.wordpress.com declare @Texto varchar(300); set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais'; with Passo1 as ( SELECT @Texto as Texto, (NullIF (charindex (': ', @Texto), 0) +2) as Pos1 ), Passo2 as ( SELECT Texto, Pos1, NullIF (charindex (' 12/', Texto, Pos1), 0) as Pos2 from Passo1 ) SELECT Texto, Pos1, Pos2, substring (Texto, Pos1, (Pos2 - Pos1)) as [Nome de pessoa] from Passo2;
Observe que bastou utilizar a função NULLIF para transformar o valor 0 em NULL nos casos em que a função CHARINDEX retorne 0. Mais uma vantagem de se utilizar CTE: o código sql fica modular, de fácil compreensão e mais simples de realizar manutenção.
O código #6 é um bom modelo a seguir para obter texto que esteja entre dois delimitadores diferentes, mas será que não há uma forma de transformar o código sql em uma função de usuário, de modo que seja mais simples ainda de se programar a obtenção do nome de pessoa?
Função de usuário para obter o nome de pessoa
No artigo “Obter o enésimo elemento de uma lista” são demonstradas várias funções de usuário que permitem obter diretamente o elemento que esteja na posição n, considerando-se que todos os delimitadores sejam iguais. Pode-se então escolher uma função que se adapte ao caso, mas antes transformando os dois separadores diferentes em um mesmo separador. Algo assim:
-- código #7
-- © José Diz / Porto SQL
declare @Texto varchar(300);
set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais';
-- substitui delimitador inicial ": "
set @Texto= replace (@Texto, ': ', ': |');
-- substitui delimitador final
set @Texto= replace (@Texto, ' 12/', '| 12/');
SELECT @Texto as Texto;
O texto substituído ficou assim:
Na data de| 12/11/2020 temos o nome do cidadão: |ALESSA ANDRO TEIXEIRA| 12/11/2020 ou mais
Entretanto, propositalmente no texto existe uma armadilha que é a presença de texto semelhante ao delimitador final mas antes do nome de pessoa. Pode não parecer mas isso às vezes ocorre na prática. Então me lembrei da função que esbocei há cerca de ano após ter lido o artigo “charindexada: Uma função diferente para quebrar strings delimitadas” no blog do Dirceu Resende, em que ele cita a função charindexada, autoria de Brunno Araújo. Na época, ao testar a charindexada encontrei algumas situações em que ela retornava resultados inesperados e resolvi escrever do zero algo semelhante.
Hoje, ao procurar pelo código fonte da função que eu estava a desenvolver, encontrei-a escondida no disco, armazenada na pasta de estudos de sql. O nome provisório dela é Extrai_Texto, é do tipo escalar e possui os seguintes parâmetros:
/* Nome: Obtem_Trecho Descrição: Função escalar que retorna trecho de texto que esteja entre os delimitadores definidos Autor: José Diz/BH Data: 11/11/2019 Versão: 0.9 Parâmetros: - @pTexto: o texto que será analisado - @pN_inicial: ocorrência de delimitador inicial a partir da qual deve-se extrair o trecho de texto. Se @pDel_inicial é NULL, então este parâmetro é ignorado - @pDel_inicial: delimitador inicial. Se nulo, extrai trecho de texto desde o ínicio de @pTexto - @pN_final: ocorrência de delimitador final a partir da qual deve-se extrair o trecho de texto. Se @pDel_inicial é NULL, então este parâmetro é ignorado - @pDel_final: delimitador final. Se nulo, extrai trecho de texto até o final de @pTexto - @pModo: indica o tipo de retorno da função: 1 retorna o trecho de texto; 2: debug */
A versão, 0.9, indica que ela ainda não está finalizada (não me lembro mais o que falta implementar/testar…) e a data de última atualização é de um ano atrás! Como coincidência não existe, percebo que é o momento de torná-la pública.
A forma de utilizar é bem simples:
- @pTexto: é o string de onde deve ser extraído o trecho;
- @pN_Inicial: indica qual ocorrência de @pDel_inicial deve ser selecionada como delimitador inicial;
- @pDel_inicial: é o string que deve ser utilizado como delimitador inicial;
- @pN_final: indica qual ocorrência de @pDel_final deve ser selecionada como delimitador final, contado após o delimitador inicial;
- @pDel_final: é o string que deve ser utilizado como delimitador final;
- @pModo: indica o modo de operação da função.
Para auxiliar na definição dos valores a passar como parâmetros desta função basta preencher as lacunas na seguinte frase:
Pesquisar em ____ pela ____-ésima ocorrência de ____; a partir desse ponto extrair o texto até encontrar a ___-ésima ocorrência de ____.
Cada lacuna corresponde a um parâmetro, na mesma sequência em que devem ser informados. Considerando-se o caso que deu origem a este artigo, a frase ficaria assim:
Pesquisar em @Texto pela primeira ocorrência de “: ”; a partir desse ponto extrair o texto até encontrar a primeira ocorrência de “ 12/”
Fácil, não?
Eis como ficaria o código #4 ao utilizar a função Extrai_Texto:
-- código #8
-- © José Diz / Porto SQL
declare @Texto varchar(300);
set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais';
SELECT @Texto as Texto,
dbo.Extrai_Texto (@Texto, 1, ': ', 1, ' 12/', 1) as [Nome de pessoa];
Bem mais simples de programar, embora às custas de uma função de usuário do tipo escalar.
Trecho inicial
A função Extrai_Texto permite extrair trecho inicial do texto, à semelhança da função charindexada, bastando não informar valores nos parâmetros @pN_Inicial e @pDel_inicial. Por exemplo, para extrair o trecho antes do nome de pessoa podemos ter a seguinte codificação:
-- código #9
-- © José Diz / Porto SQL
declare @Texto varchar(300);
set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais';
SELECT @Texto as Texto,
dbo.Extrai_Texto (@Texto, null, null, 1, ': ', 1) as [Frase inicial];
que obtemos como resultado o trecho “Na data de 12/11/2020 temos o nome do cidadão”.
Trecho final
A função Extrai_Texto também permite obter trecho final do texto, à semelhança da função charindexada, bastando não informar valores nos parâmetros @pN_final e @pDel_final. Por exemplo, para extrair a trecho após o delimitador inicial de nome de pessoa podemos ter a seguinte codificação:
-- código #10
-- © José Diz / Porto SQL
declare @Texto varchar(300);
set @Texto= 'Na data de 12/11/2020 temos o nome do cidadão: ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais';
SELECT @Texto as Texto,
dbo.Extrai_Texto (@Texto, 1, ': ', null, null, 1) as [Frase final];
que obtemos como resultado o texto “ALESSA ANDRO TEIXEIRA 12/11/2020 ou mais”.
Modo Debug
O último parâmetro da função Extrai_Texto é para selecionar o modo de operação dela: 1 é o valor normal, com a função retornando o trecho de texto entre os delimitadores inicial e final; mas se informado 2 ela retorna informações que permitem avaliar o funcionamento da função. Este segundo valor pode ser útil quando a função não retorna qualquer valor (NULL) e queremos saber a provável causa. Por exemplo, no código #10 experimente substituir
dbo.Extrai_Texto (@Texto, 1, ': ', null, null, 1)
por
dbo.Extrai_Texto (@Texto, 2, ': ', null, null, 1)
Ou seja, ao procurar a segunda ocorrência de “: ” a função retorna NULL; mas qual o motivo? Ao rodar
dbo.Extrai_Texto (@Texto, 2, ': ', null, null, 2)
temos a resposta:
Erro: não existe(m) 2 ocorrência(s) de :
ß
Como a função ainda está em versão beta, por enquanto o código fonte da função Extrai_Texto está disponível a pedido para quem tenha interesse em testá-la e auxiliar na revisão final; basta informar seu nome e endereço de e-mail abaixo em “Deixe um comentário” que envio cópia do código fonte.
Também encontrei esboço dela como função de usuário do tipo inline table_valued. Nem me lembrava de ter feito tal estudo mas a abordagem utilizada é interessante e espero que seja possível a implementação. Quem programa funções de usuário em t-sql sabe que, dependendo da complexidade do código sql da função escalar, pode ser difícil (ou mesmo impossível) convertê-la para inline table_valed.
Porto SQL 