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

Post Views 31,033 views

Neste artigo

Reading time 7 minutes

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:

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]')

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.

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 '%]%'

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.

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

SELECT * 
FROM #Teste
WHERE ISNUMERIC(Nr_Documento) = 1

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.

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:

SELECT * 
FROM #Teste
WHERE Nr_Documento NOT LIKE '%[A-z]%'

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

SELECT * 
FROM #Teste
WHERE Nr_Documento NOT LIKE '%[^0-9]%'

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:

SELECT * 
FROM #Teste
WHERE Nr_Documento NOT LIKE '%[^A-z]%'

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:

SELECT * 
FROM #Teste
WHERE Nr_Documento NOT LIKE '%[^A-z ]%' -- Reparem esse espaço no final

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:

-- Estou permitindo também, os caracteres (+), (.) e (-), além do espaço
SELECT * 
FROM #Teste
WHERE Nr_Documento LIKE '%[^A-z0-9 +.-]%'

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

SELECT 
    Nr_Documento,
    dbo.fncRecupera_Numeros(Nr_Documento)
FROM
    #Teste
WHERE
    dbo.fncRecupera_Numeros(Nr_Documento) = '12345678909'

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

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

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:

SELECT 
    Nr_Documento,
    dbo.fncRecupera_Numeros(Nr_Documento) AS Parte_Numerica,
    dbo.fncRecupera_Letras(Nr_Documento) AS Parte_Textual
FROM
    #Teste

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

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

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

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

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

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

-- 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]')

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

ALTER TABLE dbo.DadosExemplo ADD
    Parte_Numerica AS (dbo.fncRecupera_Numeros(Nr_Documento)) PERSISTED,
    Parte_Textual AS (dbo.fncRecupera_Letras(Nr_Documento)) PERSISTED

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:

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:

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

IF (OBJECT_ID('dbo.fncRecupera_Letras') IS NOT NULL) DROP FUNCTION dbo.fncRecupera_Letras

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

IF (OBJECT_ID('dbo.fncRecupera_Numeros') IS NOT NULL) DROP FUNCTION dbo.fncRecupera_Numeros

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

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

ALTER TABLE dbo.DadosExemplo ADD
    Parte_Numerica AS (dbo.fncRecupera_Numeros(Nr_Documento)) PERSISTED,
    Parte_Textual AS (dbo.fncRecupera_Letras(Nr_Documento)) PERSISTED

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:

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)

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

Gostou dessa dica de Performance? Estude mais esse tema através de alguns posts que posso recomendar:

And that's it, folks!
Espero que tenham gostado desse post e até a próxima!

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

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

Criação da base de testes

Como filtrar a parte numérica e a parte texto de uma string

Exemplo 1 – Como retornar linhas com apenas números (NOT LIKE)

Exemplo 2 – Como retornar linhas com apenas números (ISNUMERIC)

Exemplo 3 – Como retornar linhas com apenas números (NOT LIKE e Expressão Regular)

Exemplo 4 – Como retornar linhas com apenas letras

Exemplo 5 – Como retornar linhas que contém caracteres especiais

Como separar a parte numérica e a parte texto de uma string

Como melhorar a performance das consultas utilizando essas funções

You may also like...

Deixe uma respostaCancelar resposta

Criação da base de testes

Como filtrar a parte numérica e a parte texto de uma string

Exemplo 1 – Como retornar linhas com apenas números (NOT LIKE)

Exemplo 2 – Como retornar linhas com apenas números (ISNUMERIC)

Exemplo 3 – Como retornar linhas com apenas números (NOT LIKE e Expressão Regular)

Exemplo 4 – Como retornar linhas com apenas letras

Exemplo 5 – Como retornar linhas que contém caracteres especiais

Como separar a parte numérica e a parte texto de uma string

Como melhorar a performance das consultas utilizando essas funções

You may also like...

Seu SQL Server está atualizado ? Retornando a última versão do Service Pack (SP) ou Cumulative Update (CU) para a sua instância

SQL Server - How to synchronize data from a table between different databases using Trigger

[Live] – Microsoft Reactor – GitHub Copilot + Databases: more productivity writing SQL statements

Deixe uma respostaCancelar resposta