segunda-feira, 4 de julho de 2011

Remover caracteres especiais em campos do SQL Server

Function scalar no SQL SERVER para remover caracteres especiais, exatamente 33 caracteres de controle (especiais) menos o NULL ficando 32 ao todo, recomendo usar essa store function, (store procedure) em procedimentos de ETL, migração de dados ou em consultas que precisem remover esses tais caracteres da tabela ASCII.

Fica aqui o compartilhamento do conhecimento.

Abraços.

--
-- Nome Artefato/Programa..: sp_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)  O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 04/07/2011
-- Data Atual..............: 04/07/2011
-- Versao..................: 0.01
-- 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 especiais do ASCII (33) caracteres nao imprimiveis 
-- OBS.....................: Remover "caracteres especiais" em campos do SQL Server
--

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

CREATE FUNCTION sp_rm_special_chars (@stexto varchar(max))
RETURNS varchar(max)
AS 
BEGIN
 IF @stexto IS NULL BEGIN  
     RETURN NULL 
 END 
 -- Tabela ASCII
 -- Caracteres não imprimiveis, 33 ao total, menos o NUL fica 32 
 --  hex d abr ctl descricao  
 --  00  0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata      
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(1),''))  --  01  1 SOH ^A Start of Header - Início do cabeçalho
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(2),''))  --  02  2 STX ^B Start of Text - Início do texto
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(3),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(4),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(5),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(6),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(7),''))  --  07  7 BEL ^G Bell - Campainha
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(8),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(9),''))  --  09  9 HT  ^I  Horizontal Tabulation - Tabulação horizontal
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(10),'')) --  0A 10 LF  ^J Line-Feed - Alimenta linha
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(11),'')) --  0B 11 VT  ^K  Vertical Tabulation - Tabulação vertical
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(12),'')) --  0C 12 FF  ^L Form-Feed - Alimenta formulário
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(13),'')) --  13 19 DC3 ^S Device-Control 3
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(14),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(15),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(16),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(17),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(18),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(19),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(20),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(21),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(22),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(23),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(24),'')) -- 18 24 CAN ^X Cancel
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(25),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(26),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(27),'')) -- 1B 27 ESC ^[ Escape
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(28),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(29),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(30),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(31),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(127),'')) -- 7F127 DEL ^?  Delete     

 RETURN @stexto     
END
GO

-- Exemplo de uso: 

-- SELECT texto = dbo.sp_rm_special_chars ('legal e bom'), getdate() as data;  -- 'legal e bom' 
-- SELECT texto = dbo.sp_rm_special_chars ('NULL'), getdate() as data;  -- 'NULL'
-- SELECT texto = dbo.sp_rm_special_chars (NULL), getdate() as data;   -- NULL

9 comentários:

  1. COLLATE sql_latin1_general_cp1251_ci_as

    ResponderExcluir
    Respostas
    1. Também é uma opção interessante, mudando o collation para remover caracteres especiais, mas prefiro a function com replace, pois não é necessário parar o bd, apesar de não ter muita performance, a intenção deste artigo é dar opções e não ser a solução definitiva, entrentanto caso queira mudar o collation tem um passo a passo nesse artigo também de minha autoria: http://emersonhermann.blogspot.com.br/2012/04/como-alterar-o-collation-de-um-bd-no.html
      Seja Feliz!

      Excluir
  2. Olá.
    O que acham da solução no link abaixo?

    http://www.devmedia.com.br/eliminando-caracteres-especiais-de-strings-no-sql-server-collate/25286

    ResponderExcluir
    Respostas
    1. Olá leandromd,

      Interessante o artigo descrito, vejo como mais uma forma de se chegar ao mesmo resultado, porém ainda prefiro a técnica de substituir apenas os 32 caracteres especiais da tabela ASCII (descrita aqui http://emersonhermann.blogspot.com.br/2011/07/remover-caracteres-especiais-em-campos.html).

      Seguem os motivos:

      1) Não remove os caracteres acentuados e nem altera a caixa baixa ou alta das letras.
      2) Não modifica cedilha por C.
      3) Em bases com textos internacionalizados, não irá ter nenhuma mudança.
      4) Apenas remove os 32 caracteres especiais.
      5) Eu sei o que está sendo removido (32 caracteres especiais), como DBA detesto não saber o que está sendo alterado nos meus BDs.
      6) Não altera nada na base apenas remove 32 caracteres especiais da tabela ASCII, nada mais.
      7) Já usei em produção com sucesso.

      Espero ter ajudado.

      Seja feliz! :)

      Excluir
    2. Mais uma leandromd, complementando ...

      A solução proposta no link elimina caracteres especiais e não especiais em SQL Server, para os meus problemas do dia a dia não atende.

      A verdade dói, mas tem que ser dita. :)

      Vai fundo!!!

      Excluir
  3. DECLARE @TEXTO VARCHAR(8000)
    SET @TEXTO = 'º ou ª 1989898'

    DECLARE @RESULTADO VARCHAR(8000)

    SET @RESULTADO = ''

    ;WITH SPLIT AS
    (
    SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
    UNION ALL
    SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
    FROM SPLIT
    WHERE ID < LEN(@TEXTO)
    )

    SELECT @RESULTADO += (CASE WHEN LETRA COLLATE sql_latin1_general_cp1251_ci_as LIKE '[A-z-0-9]' THEN LETRA ELSE ' ' END)
    FROM SPLIT
    OPTION(MAXRECURSION 0)

    SELECT @RESULTADO

    ResponderExcluir
    Respostas
    1. Show!!! Mil e uma formas de se chegar ao mesmo resultado. Parabéns!!!

      Excluir