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;
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
ResponderExcluirOu 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.
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 :).
ExcluirObrigado pela informação, me ajudou.
ResponderExcluir