Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como filtrar e separar o número e o texto de uma string

Visualizações: 30.960 views
Tempo de Leitura: 7 minutos

Fala pessoal!
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:

Resultado:

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.

Resultado:

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.

Observação: ISNUMERIC retorna 1 para alguns caracteres que não são números, como mais (+), menos (-) e símbolos de moeda válidos como o cifrão ($).
Script utilizado:

Resultado:

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.

Se você não sabia que era possível utilizar expressão regular (Regex ou Regexp) no SQL Server ou quiser se aprofundar mais, dê uma lida no meu artigo SQL Server – Como utilizar expressões regulares (RegExp) no seu banco de dados.

Exemplo:

Resultado:

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):

Resultado:

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:

Resultado:

Ué.. Não retornou nada! Mas tinha uma string “Dirceu Resende” que deveria retornar.. Ah, mas tem o espaço.. Vamos tentar de novo:

Resultado:

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:

Resultado:

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:

Resultado:

Também consigo fazer a mesma coisa com a parte textual, onde vou retornar as linhas cuja frase textual seja “Dirceu Resende”:

Resultado:

Analisando todas as linhas dessa tabela, agora utilizando também as 2 funções:

Resultado:

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

Código fonte da fncRecupera_Numeros

Importante: De modo geral, funções de usuário (UDF) podem causar grandes problemas de performance, especialmente se utilizados na cláusula WHERE/JOIN e em tabelas muito grandes. Para saber mais sobre isso, leia o meu post SQL Server – Utilizando colunas calculadas (ou colunas computadas) para Performance Tuning.

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:

Agora vou tentar incluir as colunas calculadas usando as funções:

E o resultado é uma mensagem de erro:

Msg 4936, Level 16, State 1, Line 39
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:

Agora vou tentar incluir novamente as colunas calculadas usando as funções, que agora foram criadas como determinísticas (WITH SCHEMABINDING):

E o comando foi executado com sucesso. Vamos consultar novamente a nossa tabela:

E agora, podemos indexar essas colunas normalmente:

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):

E é isso aí, pessoal!
Espero que tenham gostado desse post e até a próxima!