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.
Olá Emerson.
ResponderExcluirTive alguns casos parecidos com esse em uma migração de SQL para Postgres.Porém resolvemos com Substring mesmo.
Ótimo post.
Parabéns, muito útil a postagem e também a dica do Felipe...
Excluir:)
ExcluirQue bom! Então fica contribuição, caso precise fazer com strings variaveis.
ResponderExcluirMuito bom! Simples e objetivo.
ResponderExcluirEu 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!
Felipe, obrigado pela dica vou providenciar os ajustes :)
ResponderExcluirPor nada!
ResponderExcluirSeu código foi muito útil para um rotina que eu precisava no meu trabalho!
Abraço!
Ok! Ajustes feitos.
ResponderExcluirObrigado Felipe.
Me ajudou e muito, valeu!
ResponderExcluirFico feliz! Sucesso!
Excluir13 anos depois e ainda serve kkkk
ResponderExcluirEssa é a parte boa de quem trabalha com banco de dados, sua experiência não é invalidada. Sucesso!
Excluir