quarta-feira, 24 de julho de 2013

Remover caracteres especiais em campos do Postgres

Function scalar para Postgres, com objetivo de remover caracteres especiais, exatamente 33 caracteres de controle (especiais) menos o NULL ficando 32 ao todo, recomendo usar essa store function 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..: usf_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) 
-- ........................: O Peregrino / http://emersonhermann.blogspot.com
-- Data Inicio ............: 09/10/2008
-- Data Atual..............: 24/07/2012
-- Versao..................: 0.02
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store function para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis 
-- OBS.....................: 
--
--habilitando linguagem plpgsql 
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; 
-- languages 
--SELECT * FROM pg_language;
-- Apaga function se existir 
DROP FUNCTION IF EXISTS usf_rm_special_chars (TEXT);
CREATE OR REPLACE FUNCTION usf_rm_special_chars (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo TEXT;
BEGIN
  -- Tabela ASCII
  -- Caracteres não imprimiveis, 33 ao total, menos o NUL ficam 32 
                                                    --  hex d abr ctl descricao  
                                                    --  00  0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata 
  stextonovo := translate(stexto, chr(1),'');       --  01  1 SOH ^A Start of Header - Início do cabeçalho
  stextonovo := translate(stextonovo, chr(2),'');   --  02  2 STX ^B Start of Text - Início do texto
  stextonovo := translate(stextonovo, chr(3),'');   --  03  3 ETX ^C End of Text - Fim do texto
  stextonovo := translate(stextonovo, chr(4),'');   --  04  4 EOT ^D End of Tape - Fim de fita
  stextonovo := translate(stextonovo, chr(5),'');   --  05  5 ENQ ^E Enquire - Interroga identidade do terminal
  stextonovo := translate(stextonovo, chr(6),'');   --  06  6 ACK ^F Acknowledge - Reconhecimento
  stextonovo := translate(stextonovo, chr(7),'');   --  07  7 BEL ^G Bell - Campainha
  stextonovo := translate(stextonovo, chr(8),'');   --  08  8 BS  ^H  Back-space - Espaço atrás
  stextonovo := translate(stextonovo, chr(9),'');   --  09  9 HT  ^I  Horizontal Tabulation - Tabulação horizontal
  stextonovo := translate(stextonovo, chr(10),'');  --  0A 10 LF  ^J Line-Feed - Alimenta linha
  stextonovo := translate(stextonovo, chr(11),'');  --  0B 11 VT  ^K  Vertical Tabulation - Tabulação vertical
  stextonovo := translate(stextonovo, chr(12),'');  --  0C 12 FF  ^L Form-Feed - Alimenta formulário
  stextonovo := translate(stextonovo, chr(13),'');  --  0D 13 CR  ^M Carriage-Return - Retorno do carro (enter)
  stextonovo := translate(stextonovo, chr(14),'');  --  0E 14 SO  ^N Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.)
  stextonovo := translate(stextonovo, chr(15),'');  --  0F 15 SI  ^O Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.)
  stextonovo := translate(stextonovo, chr(16),'');  --  10 16 DLE ^P Data-Link Escape
  stextonovo := translate(stextonovo, chr(17),'');  --  11 17 DC1 ^Q Device-Control 1
  stextonovo := translate(stextonovo, chr(18),'');  --  12 18 DC2 ^R Device-Control 2
  stextonovo := translate(stextonovo, chr(19),'');  --  13 19 DC3 ^S Device-Control 3
  stextonovo := translate(stextonovo, chr(20),'');  --  14 20 DC4 ^T Device-Control 4
  stextonovo := translate(stextonovo, chr(21),'');  --  15 21 NAK ^U Neg-Acknowledge - Não-reconhecimento
  stextonovo := translate(stextonovo, chr(22),'');  --  16 22 SYN ^V Synchronous Idle
  stextonovo := translate(stextonovo, chr(23),'');  --  17 23 vETB^W  End-of-Transmission Block
  stextonovo := translate(stextonovo, chr(24),'');  --  18 24 CAN ^X Cancel
  stextonovo := translate(stextonovo, chr(25),'');  --  19 25 EM  ^Y  End-Of-Medium
  stextonovo := translate(stextonovo, chr(26),'');  --  1A 26 SUB ^Z Substitute
  stextonovo := translate(stextonovo, chr(27),'');  --  1B 27 ESC ^[ Escape
  stextonovo := translate(stextonovo, chr(28),'');  --  1C 28 FS  ^\  File Separator
  stextonovo := translate(stextonovo, chr(29),'');  --  1D 29 GS  ^] Group Separator
  stextonovo := translate(stextonovo, chr(30),'');  --  1E 30 RS  ^^  Record Separator
  stextonovo := translate(stextonovo, chr(31),'');  --  1F 31 US  ^_ Unit Separator
  stextonovo := translate(stextonovo, chr(127),''); --  7F127 DEL ^?  Delete

  RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;


Test 1

Test 2



-- string com caracteres especiais, formato 2 
SELECT 'SEM STRESS... ' || chr(1) || 'TECLA  ENTER: ' || chr(13) || ' TECLA ESC: ' || chr(27) || ' TESTE OK ' AS test3; 
-- string sem caracteres espciais usando a function, formato 2 
SELECT usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA  ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test4; 

-- verificando tamanho da string, 53 caracteres, incluindo 3 especiais 
SELECT length('SEM STRESS... ' || chr(1) || 'TECLA  ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test5; 
-- verificando tamanho da string, 50 caracteres, removido 3 especiais
SELECT length(usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA  ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ')) AS test6; 


2 comentários:

  1. Caro Peregrino, sugiro utilizar expressões regulares para matar este tipo de problema com mais elegância. Tanto o PostgreSQL quanto o Oracle tem suporte nativo. Isto permite filtrar situações bem complexas como a que você deseja aqui utilizando funções nativas do SGDB. No caso do PostgreSQL, você pode utilizar o regexp_replace, regexp_matche, regexp_split_to_array e ainda o regexp_split_to_table. Vide http://www.postgresql.org/docs/current/static/functions-string.html

    Ou seja, conhecendo expressões regulares (recomendo o excelente livro do Aurélio Verde) e com essas funções aí, você faz o seu SGDB retornar exatamente o que você quer, quando quiser e como quiser. Boa diversão.

    ResponderExcluir
    Respostas
    1. Prezado Fabio Telles, valeu a dica, muito boa, sempre é bom ter opções, seu comentário enriqueceu muito o assunto, ainda mais vindo de uma Autoridade Nacional em Postgres e Oracle :) curto muito seus artigos no blog Savepoint, Nas próximas talvez postarei algo no sentido de ER e BD. Obrigado :).

      Excluir