sexta-feira, 15 de julho de 2011

Remover Letras em campos do SQL Server



Remover Letras em campos do SQL Server

Vez por outra, precisamos de fazer uso de campos concatenados com números e strings e em alguns casos só procisamos
dos números nos joins ou em ETL para extração de dados.

Poderiamos simplesmente usar a function substring se o campo string fosse de tamanho fixo, mas não é, e nesses casos a coisa complica.

A exemplo, um campo nome id_tabela de uma tabela qualquer, em algumas ocorrencias, isto é, linhas o id_tabela está ABCZ00001
e em outra ZCBA00002X, e uma outra linha também poderia está preenchida dessa forma ABZ00003.

Então sendo assim:

Aqui segue a dica, de uma function desenvolvida em T-SQL que resolve esse problema:

--
--
-- Nome Artefato/Programa..: sp_rm_letters.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) adaptado de http://www.softstuff-consulting.com/kbase/showkb.asp?id=56  
-- ........................: Com colobaração de Felipe (felipegramos [at] yahoo.com.br)
-- Data Inicio ............: 08/07/2011
-- Data Atualizacao........: 16/08/2011
-- Versao..................: 0.03
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2008
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remocao de caracteres de uma string deixando só os números
-- OBS.....................: 
--

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[sp_rm_letters]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION sp_rm_letters
GO


CREATE FUNCTION sp_rm_letters(@number varchar(max)) RETURNS varchar(max) AS
BEGIN
 DECLARE @c int
 SET @c=65
 WHILE @c<(65+62) BEGIN
  SET @number=replace(@number,char(@c),'')
  SET @c=@c+1
 END
 RETURN(@number)
END

/*
-- Abaixo, letras, caracteres que serão removidos da string, pela function sp_rm_letters:
ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ

-- Chamando a função via PRINT 
PRINT dbo.sp_rm_letters('ABC123Z4') -- Retorno 1234

-- Ou
SELECT dbo.sp_rm_letters('ABC123Z4'); -- Retorno 1234

-- Uma implementação da função 

SELECT * FROM tabela1 t1 JOIN tabela2 t2 ON t1.id_campo=dbo.sp_rm_letters(t2.id_campo);

-- A função sp_rm_letters não remove caracteres especiais mas pode ser combinada com a function sp_rm_special_chars (publicada também no blog http://emersonhermann.blogspot.com), para remover caracteres especiais.
SELECT dbo.sp_rm_special_chars(dbo.sp_rm_letters('éab~ZzcABC123éáúZ4z|||2'+char(13)+'ç['+char(127)));


Mais uma vez espero ter ajudado.

9 comentários:

  1. Olá Emerson.

    Tive alguns casos parecidos com esse em uma migração de SQL para Postgres.Porém resolvemos com Substring mesmo.

    Ótimo post.

    ResponderExcluir
  2. Que bom! Então fica contribuição, caso precise fazer com strings variaveis.

    ResponderExcluir
  3. Muito bom! Simples e objetivo.

    Eu só colocaria uma atualização para retirar TODOS os caracteres mesmo, menos os números. Porque do jeito que está, as letras minúsculas e caracteres especiais permaneceriam.

    Algo como @c = 0 e o WHILE @c <= 255. Se preocupando em dentro do while não pegar o valor correspondente aos números (do 48 ao 57).

    Abraço!

    ResponderExcluir
  4. Felipe, obrigado pela dica vou providenciar os ajustes :)

    ResponderExcluir
  5. Felipe - felipegramos@yahoo.com.br22 de julho de 2011 17:39

    Por nada!

    Seu código foi muito útil para um rotina que eu precisava no meu trabalho!

    Abraço!

    ResponderExcluir
  6. Ok! Ajustes feitos.
    Obrigado Felipe.

    ResponderExcluir
  7. Este blog é uma representação exata de competências. Eu gosto da sua recomendação. Um grande conceito que reflete os pensamentos do escritor. Consultoria RH

    ResponderExcluir