Uma pergunta comum que vejo de muitas pessoas novas ao T-SQL é como encontrar dados numa corda e extraí-los. Este é um pedido muito comum, uma vez que as nossas bases de dados contêm muitas cordas. Verificamos frequentemente que as pessoas que utilizam aplicações incorporam informação numa cadeia de caracteres, com a expectativa de que o programa seja capaz de remover facilmente essa informação mais tarde. Neste artigo, vou analisar como extrair estes dados usando SUBSTRING, CHARINDEX, e PATINDEX.
Este é um regresso ao artigo básico que espero seja útil para os programadores e DBAs que são novos no SQL Server e que procuram melhorar as suas competências. Sinta-se à vontade para transmitir isto.
Ponto de Venda Consistente
Um exemplo é um número de factura ou número de PO. Tenho visto frequentemente estes dados embutidos em campos de texto, com um requisito posterior para extrair este número do campo. Este é um tipo comum de dados que é adicionado a um campo numa tabela algures, como por exemplo numa tabela de Clientes. Poderemos ter utilizadores, ou uma aplicação, a decidir adicionar estes dados para desnormalizar os nossos dados.
P>Posto que temos uma tabela que contém informação como esta:
CREATE TABLE Customers( CustomerID INT, CustomerName VARCHAR(500), CustomerNotes VARCHAR(MAX), Active TINYINT);GOINSERT dbo.Customers ( CustomerID , CustomerName , CustomerNotes , Active ) VALUES ( 1, 'Acme Inc', 'Last PO:20154402', 1) , ( 2, 'Roadrunner Enterprises', 'Last PO:20140322', 1 ) , ( 3, 'Wile E. Coyote and Sons', 'Unreliable payments', 0)
Se eu olhar para os dados, vemos que alguém decidiu incluir informação importante no campo de notas. Tenho a certeza de que muitas pessoas experientes se irão encolher neste uso dos campos numa tabela, mas isto acontece mais vezes do que muitos de nós gostaríamos.
Se eu agora quiser tirar o PO deste campo, talvez para um relatório que seja necessário, ou talvez porque vou ETL estes dados para um local mais apropriado, posso usar a função SUBSTRING em T-SQL. Utilizo esta função quando sei onde dentro de uma string estou a procurar obter dados.
Neste caso, posso ver que os primeiros 8 caracteres do campo CustomerNotes são frequentemente “Last PO:”. Com isto, posso começar pelo 9º carácter e depois obter os próximos 8 caracteres (comprimento do PO). Vou utilizar esta consulta.
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, 9, 8)FROM dbo.Customers
Esta irá devolver as OP, mas obtenho alguns outros dados.
Sem preocupações, posso facilmente filtrar isto (uma discussão para outro artigo).
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, 9, 8)FROM dbo.Customers WHERE customerNotes LIKE '%PO%'
Now, I’m done, right? Bem, talvez não.
Um PO Inconsistente
Nos dados que vi até agora, o número do PO está muito bem posicionado. No entanto, suponhamos que nem todas as nossas pessoas de entrada de dados trabalham com clientes da mesma maneira. Aqui um pouco mais de dados para mostrar o que quero dizer:
INSERT dbo.Customers ( CustomerID , CustomerName , CustomerNotes , Active ) VALUES ( 4, 'Beep Beep Enterprises', 'Remember their slogan: We go fast. Last PO:20154402', 1) , ( 5, 'Goldberg Supplies', 'Preferred. Last PO:20140322', 1 ) , ( 6, 'Bugs Deliveries', 'Fast Last PO:20145554', 0)
Agora vamos correr o nosso script de cima. Obtemos estes dados:
Não é bem o que queremos. O problema aqui é que o início do SUBSTRING não é o que nós queremos. Precisamos de começar com a localização do número PO, talvez com a localização do “PO:”. Como podemos obter isso?
Temos algumas escolhas, mas introduza CHARINDEX e PATINDEX. Ambos nos permitem procurar um fio e encontrar outro fio dentro dele. Qualquer um deles pode funcionar aqui, mas deixem-me mostrar-vos como estes funcionam nos nossos dados de teste. Vou executar esta consulta:
SELECT CustomerID , CustomerNotes , 'Charindex_StartPO' = CHARINDEX('PO:', CustomerNotes) , 'Patindex_StartPO' = PATINDEX('%PO:%', CustomerNotes) , 'PO' = SUBSTRING(CustomerNotes, 9, 8) FROM dbo.Customers
E obter estes resultados:
Nota que podemos ver aqui ambas as funções retornam o mesmo valor, a posição inicial do “P” em “PO”. Existem algumas diferenças. CHARINDEX pode começar numa determinada posição na corda, enquanto que PATINDEX pode tomar wildcards. Neste caso simplista, podemos usar um.
I usarei CHARINDEX aqui, e alterarei a minha consulta a isto:
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, CHARINDEX('PO:', CustomerNotes), 8) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Isso dá-me isto, que não é o que eu quero.
Esqueci-me que CHARINDEX me dá a posição inicial do PO, por isso tenho de acrescentar a este valor. Aqui está uma consulta que funciona:
SELECT CustomerID , 'PO' = SUBSTRING(CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3, 8) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Nota que adicionei 3 ao resultado da função CHARINDEX. Aqui estão os resultados:
O PO Cresce
Parece que esta é uma boa consulta, mas imaginemos que adicionamos um pouco mais de dados.
Nota que, neste caso, temos pedidos de compra que cresceram em tamanho. Algumas têm 8 caracteres, e outras 9. Certamente que podemos tomar apenas 9 caracteres, mas podemos crescer até 10 ou mais. Além disso, temos outras notas após o pedido em lugares.
Vamos modificar a nossa consulta para ver o que podemos fazer. Adicionei uma reviravolta ao meu CHARINDEX.
SELECT CustomerID , CustomerNotes , 'Start of PO' = CHARINDEX('PO:', CustomerNotes)+3 , 'End of PO' = CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) , 'PO' = SUBSTRING(CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3, 8) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Aqui estão os resultados:
Se olharmos de perto, vemos que a nossa última entrada, com texto após o PO nos dá um resultado CHARINDEX. Isto porque estamos à procura de uma string, obtemos um 0 se nenhuma entrada for encontrada. Apenas o cliente 8 tem um espaço após o PO. Isto significa que podemos calcular o comprimento do PO para a última entrada, mas e sobre todas as outras entradas que têm um formato diferente?
Podemos usar aqui uma declaração CASE, uma vez que temos aqui duas possibilidades. Um CASO irá verificar a existência de um espaço e devolver o índice do espaço dentro da cadeia. O outro irá devolver o comprimento da própria corda, quando não existe espaço. Isto dá-me um código como este:
Update: a minha matemática estava incorrecta. Mudado de -3, para -2 no código abaixo.
SELECT CustomerID , CustomerNotes , 'Start of PO' = CHARINDEX('PO:', CustomerNotes)+3 , 'End of PO' = CASE WHEN CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) = 0 THEN LEN(CustomerNotes) ELSE CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) END , 'Real End of PO' = CASE WHEN CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) = 0 THEN LEN(customernotes) ELSE CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) END - CHARINDEX('PO:', CustomerNotes) , 'PO' = SUBSTRING(CustomerNotes , CHARINDEX('PO:', CustomerNotes)+3 , CASE WHEN CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) = 0 THEN LEN(customernotes) ELSE CHARINDEX(' ', CustomerNotes, CHARINDEX('PO:', CustomerNotes)+3) END - CHARINDEX('PO:', CustomerNotes) - 2 ) FROM dbo.Customers WHERE customerNotes LIKE '%PO%';
Se olharmos para este código, é muito semelhante ao código SUBSTRING que usávamos antes, mas agora, em vez de um comprimento fixo, 8, para o número de caracteres a retornar, estamos a retornar valores com uma fórmula. A fórmula é essencialmente o fim real do PO (a 5ª coluna do conjunto de resultados) e o início do PO. Existe uma declaração CASO para quando obtivermos um zero.
Agora, se fizermos as contas, podemos ver quanto tempo é cada PO. Para a maioria das OP, isto é 8 caracteres (11 caracteres após o início do “P” em “PO:”), mas 9 caracteres para o cliente 7 e 11 para o cliente 8.
alguns de vós podem interrogar-se sobre os -3 no código, mas se se lembrarem das regras de aritmética, na verdade, eu levei o menos até à quantidade que representa o início do número da OP.
Conclusão
Este não é o fim das possibilidades de OP incorporadas num campo de notas. Poderia ter algo como “testar PO: 201530444. Novo teste” e isso causaria problemas com o nosso código. De facto, há muitos outros casos que eu teria de tratar no mundo real.
Este artigo veio de alguns problemas de extracção de cordas que tive de resolver no mundo real, e estes tipos de problemas ocorrem de facto. Espero ter-lhe dado algumas habilidades para praticar que o ajudarão na manipulação de cordas do seu SQL Server.
Como com quaisquer técnicas que possa aprender aqui, certifique-se de que avalia o impacto do desempenho. Execute o seu código contra um grande conjunto de dados de teste e determine o quão bem esta técnica pode funcionar em relação a outras técnicas. Recomendo que utilize uma tabela de contagem para gerar dados numa escala maior do que as suas tabelas de produção.
Manipulações de cordas podem ser computacionalmente caras no SQL Server, por isso certifique-se de que compreende o impacto das suas escolhas antes de implementar o código num sistema de produção.