Hey guys!
Nesse artigo, vou compartilhar com vocês uma série de opções para filtrar e separar o número e o texto de uma string utilizando funções e comandos T-SQL no SQL Server, o que é uma necessidade muito comum no dia a dia de quem desenvolve consultas SQL diversas ou quer validar os dados de alguma tabela.
Criação da base de testes
Para os exemplos que vou demonstrar nesse artigo, vou utilizar o seguinte script abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste CREATE TABLE #Teste ( Nr_Documento VARCHAR(50) ) INSERT INTO #Teste VALUES ('12345678909'), ('123.456.789-09'), ('Dirceu12345Resende678909.com'), (' 12345678909 '), ('"12345678909"'), ('d12345678909'), ('12345+6789-09'), ('123456.789'), ('R$ 123456.789'), ('$ 123456.789'), ('+123456.789'), ('-123456.789'), ('Dirceu Resende'), ('Dirceu[Resende]') |
Result:
Como filtrar a parte numérica e a parte texto de uma string
Uma necessidade muito comum também, é filtrar a parte numérica e a parte texto de uma string. Vamos aprender como fazer isso?
Exemplo 1 – Como retornar linhas com apenas números (NOT LIKE)
Uma das formas frequentes que vejo pessoas tentando atingir esse objetivo, é utilizando o operador NOT LIKE para ignorar as linhas que possuem determinados caracteres.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SELECT * FROM #Teste WHERE Nr_Documento IS NOT NULL AND Nr_Documento NOT LIKE '%a%' AND Nr_Documento NOT LIKE '%b%' AND Nr_Documento NOT LIKE '%c%' AND Nr_Documento NOT LIKE '%d%' AND Nr_Documento NOT LIKE '%e%' AND Nr_Documento NOT LIKE '%f%' AND Nr_Documento NOT LIKE '%g%' AND Nr_Documento NOT LIKE '%h%' AND Nr_Documento NOT LIKE '%i%' AND Nr_Documento NOT LIKE '%j%' AND Nr_Documento NOT LIKE '%k%' AND Nr_Documento NOT LIKE '%l%' AND Nr_Documento NOT LIKE '%m%' AND Nr_Documento NOT LIKE '%n%' AND Nr_Documento NOT LIKE '%o%' AND Nr_Documento NOT LIKE '%p%' AND Nr_Documento NOT LIKE '%q%' AND Nr_Documento NOT LIKE '%r%' AND Nr_Documento NOT LIKE '%s%' AND Nr_Documento NOT LIKE '%t%' AND Nr_Documento NOT LIKE '%u%' AND Nr_Documento NOT LIKE '%v%' AND Nr_Documento NOT LIKE '%x%' AND Nr_Documento NOT LIKE '%w%' AND Nr_Documento NOT LIKE '%z%' AND Nr_Documento NOT LIKE '%y%' AND Nr_Documento NOT LIKE '%.%' AND Nr_Documento NOT LIKE '%-%' AND Nr_Documento NOT LIKE '%"%' AND Nr_Documento NOT LIKE '%[%' AND Nr_Documento NOT LIKE '%]%' |
Result:
Bem.. O resultado foi alcançado, mas a query acabou ficando muito grande, difícil de manter, performance ruim e pode precisar de mudanças conforme o collation, especialmente se estiver utilizando um collation que diferencie maiúsculas de minúsculas.
Exemplo 2 – Como retornar linhas com apenas números (ISNUMERIC)
Visando tentar entregar uma solução mais simples e eficaz, vamos tentar utilizar outra forma de se conseguir esse resultado através da função ISNUMERIC(), que irá retornar 0 se o parâmetro informado tiver qualquer caractere que não seja numérico.
|
1 2 3 |
SELECT * FROM #Teste WHERE ISNUMERIC(Nr_Documento) = 1 |
Result:
Mais uma vez, o resultado desejado foi alcançado e a query ficou bem simples e objetiva. Mas e se eu quiser retornar as linhas que contém apenas números mesmo, ignorando até decimais, sinais de (+), (-), símbolos de moedas, etc?
Exemplo 3 – Como retornar linhas com apenas números (NOT LIKE e Expressão Regular)
Uma outra forma de conseguir retornar linhas com apenas números é utilizando NOT LIKE e Expressões Regulares no SQL Server.
Exemplo:
|
1 2 3 |
SELECT * FROM #Teste WHERE Nr_Documento NOT LIKE '%[A-z]%' |
Result:
Nesse caso, retornamos linhas que não possuem letras, mas ainda está retornando símbolos..
Vamos mudar um pouco a nossa query e tentar novamente, mas desta vez, quero retornar as linhas onde NÃO TENHA nenhum caractere que NÃO ESTEJA no intervalo de 0 a 9 (apenas números):
|
1 2 3 |
SELECT * FROM #Teste WHERE Nr_Documento NOT LIKE '%[^0-9]%' |
Result:
Show!! Agora sim, finalmente tenho o resultado que eu queria! Somente as linhas que só possuem números.
Exemplo 4 – Como retornar linhas com apenas letras
Num cenário agora inverso ao anterior, quero fazer o contrário. Quero retornar linhas com apenas letras. A query então, é tão simples quanto a do nosso último exemplo, certo?
Exemplo:
|
1 2 3 |
SELECT * FROM #Teste WHERE Nr_Documento NOT LIKE '%[^A-z]%' |
Result:
Ué.. Não retornou nada! Mas tinha uma string “Dirceu Resende” que deveria retornar.. Ah, mas tem o espaço.. Vamos tentar de novo:
|
1 2 3 |
SELECT * FROM #Teste WHERE Nr_Documento NOT LIKE '%[^A-z ]%' -- Reparem esse espaço no final |
Result:
Prontinho 🙂
Exemplo 5 – Como retornar linhas que contém caracteres especiais
Caso você queira identificar quais linhas possuem caracteres especiais, também é bem fácil.
Exemplo:
|
1 2 3 4 |
-- Estou permitindo também, os caracteres (+), (.) e (-), além do espaço SELECT * FROM #Teste WHERE Nr_Documento LIKE '%[^A-z0-9 +.-]%' |
Result:
Funcionou! Isso é muito útil para identificar possíveis erros em integrações de dados e aqueles caracteres invisíveis e ocultos no meio da string (Leia mais sobre isso no meu artigo SQL Server – Como identificar e substituir “coisas estranhas” (caracteres ocultos) em strings e tabelas)
Como separar a parte numérica e a parte texto de uma string
Diferente dos exemplos acima, aqui o que eu quero mesmo é retornar a parte numérica das linhas, onde ela seja igual a “12345678909”, mesmo que tenha outros caracteres no meio da string.
Exemplo:
|
1 2 3 4 5 6 7 |
SELECT Nr_Documento, dbo.fncRecupera_Numeros(Nr_Documento) FROM #Teste WHERE dbo.fncRecupera_Numeros(Nr_Documento) = '12345678909' |
Result:
Também consigo fazer a mesma coisa com a parte textual, onde vou retornar as linhas cuja frase textual seja “Dirceu Resende”:
|
1 2 3 4 5 6 7 8 |
SELECT Nr_Documento, dbo.fncRecupera_Numeros(Nr_Documento) AS Parte_Numerica, dbo.fncRecupera_Letras(Nr_Documento) AS Parte_Textual FROM #Teste WHERE dbo.fncRecupera_Letras(Nr_Documento) IN ('Dirceu Resende', 'DirceuResende') |
Result:
Analisando todas as linhas dessa tabela, agora utilizando também as 2 funções:
|
1 2 3 4 5 6 |
SELECT Nr_Documento, dbo.fncRecupera_Numeros(Nr_Documento) AS Parte_Numerica, dbo.fncRecupera_Letras(Nr_Documento) AS Parte_Textual FROM #Teste |
Result:
Outro exemplo para ajudar a fixar a ideia:
Gostou dessas funções, né? Segue abaixo o código-fonte das duas:
Código fonte da fncRecupera_Letras
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE FUNCTION [dbo].[fncRecupera_Letras] ( @str VARCHAR(500) ) RETURNS VARCHAR(500) BEGIN DECLARE @startingIndex INT = 0 WHILE (1 = 1) BEGIN SET @startingIndex = PATINDEX('%[^a-Z|^ ]%', @str) IF @startingIndex <> 0 BEGIN SET @str = REPLACE(@str, SUBSTRING(@str, @startingIndex, 1), '') END ELSE BREAK END RETURN @str END |
Código fonte da fncRecupera_Numeros
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE FUNCTION [dbo].[fncRecupera_Numeros] ( @str VARCHAR(500) ) RETURNS VARCHAR(500) BEGIN DECLARE @startingIndex INT = 0 WHILE (1 = 1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @str) IF @startingIndex <> 0 BEGIN SET @str = REPLACE(@str, SUBSTRING(@str, @startingIndex, 1), '') END ELSE BREAK END RETURN @str END |
Outra dica: De modo geral, funções de usuário (UDF) são lentas para executar em tabelas grandes. Uma outra alternativa para melhorar bastante a performance de funções, é utilizar funções SQLCLR, conforme demonstro os ganhos no artigo SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function.
Como melhorar a performance das consultas utilizando essas funções
Uma outra dica legal, é que dá até pra criar uma coluna calculada, indexada, para fazer as buscas bem mais rápido:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- Crio a tabela física no mesmo database das funções IF (OBJECT_ID('dbo.DadosExemplo') IS NOT NULL) DROP TABLE dbo.DadosExemplo CREATE TABLE dbo.DadosExemplo ( Nr_Documento VARCHAR(50) ) INSERT INTO dbo.DadosExemplo VALUES ('12345678909'), ('123.456.789-09'), ('Dirceu12345Resende678909.com'), (' 12345678909 '), ('"12345678909"'), ('d12345678909'), ('12345+6789-09'), ('123456.789'), ('R$ 123456.789'), ('$ 123456.789'), ('+123456.789'), ('-123456.789'), ('Dirceu Resende'), ('Dirceu[Resende]') |
Agora vou tentar incluir as colunas calculadas usando as funções:
|
1 2 3 |
ALTER TABLE dbo.DadosExemplo ADD Parte_Numerica AS (dbo.fncRecupera_Numeros(Nr_Documento)) PERSISTED, Parte_Textual AS (dbo.fncRecupera_Letras(Nr_Documento)) PERSISTED |
E o resultado é uma mensagem de erro:
Computed column ‘Parte_Numerica’ in table ‘DadosExemplo’ cannot be persisted because the column is non-deterministic.
Como já expliquei sobre esse erro no artigo SQL Server – Utilizando colunas calculadas (ou colunas computadas) para Performance Tuning, para utilizar função como coluna calculada, a função precisa ser determinística. Nesse caso, para transformar essas 2 funções em determinísticas, vamos adicionar a cláusula WITH SCHEMABINDING no comando CREATE FUNCTION:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
IF (OBJECT_ID('dbo.fncRecupera_Letras') IS NOT NULL) DROP FUNCTION dbo.fncRecupera_Letras GO CREATE FUNCTION [dbo].[fncRecupera_Letras] ( @str VARCHAR(500) ) RETURNS VARCHAR(500) WITH SCHEMABINDING BEGIN DECLARE @startingIndex INT = 0 WHILE (1 = 1) BEGIN SET @startingIndex = PATINDEX('%[^a-Z|^ ]%', @str) IF @startingIndex <> 0 BEGIN SET @str = REPLACE(@str, SUBSTRING(@str, @startingIndex, 1), '') END ELSE BREAK END RETURN @str END GO IF (OBJECT_ID('dbo.fncRecupera_Numeros') IS NOT NULL) DROP FUNCTION dbo.fncRecupera_Numeros GO CREATE FUNCTION [dbo].[fncRecupera_Numeros] ( @str VARCHAR(500) ) RETURNS VARCHAR(500) WITH SCHEMABINDING BEGIN DECLARE @startingIndex INT = 0 WHILE (1 = 1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @str) IF @startingIndex <> 0 BEGIN SET @str = REPLACE(@str, SUBSTRING(@str, @startingIndex, 1), '') END ELSE BREAK END RETURN @str END GO |
Agora vou tentar incluir novamente as colunas calculadas usando as funções, que agora foram criadas como determinísticas (WITH SCHEMABINDING):
|
1 2 3 |
ALTER TABLE dbo.DadosExemplo ADD Parte_Numerica AS (dbo.fncRecupera_Numeros(Nr_Documento)) PERSISTED, Parte_Textual AS (dbo.fncRecupera_Letras(Nr_Documento)) PERSISTED |
E o comando foi executado com sucesso. Vamos consultar novamente a nossa tabela:
E agora, podemos indexar essas colunas normalmente:
|
1 2 |
CREATE NONCLUSTERED INDEX SK01_DadosExemplo ON dbo.DadosExemplo(Parte_Numerica) INCLUDE(Nr_Documento) WITH(DATA_COMPRESSION=PAGE) CREATE NONCLUSTERED INDEX SK02_DadosExemplo ON dbo.DadosExemplo(Parte_Textual) INCLUDE(Nr_Documento) WITH(DATA_COMPRESSION=PAGE) |
E agora quando eu quero consultar essa coluna calculada, ela terá um desempenho excelente (claro, dependendo de como a consulta é feita e como os índices foram criados):
- SQL Server – Introdução ao estudo de Performance Tuning
- Entendendo o funcionamento dos índices no SQL Server
- SQL Server – Dicas de Performance Tuning: Conversão implícita? NUNCA MAIS!
- SQL Server – Dicas de Performance Tuning: Qual a diferença entre Seek Predicate e Predicate?
- O SQL Server é extremamente rápido. Você que não sabe usar!
And that's it, folks!
Espero que tenham gostado desse post e até a próxima!
Portuguese 
English