Re: Enc: Help to replace caracter

On Fri, Nov 14, 2008 at 12:17 PM, paulo matadr <saddoness(at)yahoo(dot)com(dot)br> wrote:
>
> I Need to replace string (" ) in the situation below :
>
> select clie_nmcliente from cadastro.cliente where clie_nmcliente like
> '%"%';
>
> result:
> JOANA D"ARCALMEIDA"
> EMLURB "P M R."
> CECILIA D"CAGNO"
> HELENA FERREIRA D"FREITAS"
> JOSE M. "BARRACA DO BOLA"
> FORTE" DUNAS BAR"
> JOANA D"ARC R. DE SOUZA
> ASSEMBLEIA DE DEUS"
> USINA SALGADO"SUPRIMENTO
> JOSE MOURA 'BIGODE"
> BEATRIZ MEDEIROS D"EMERY
> Any help me to create pgPL/sql or funcion to replace ( " ) to null value,
> I have many table with this.
> Thanks for help
>

hi, with the regexp_replace function can replace a character by
another, for example:

SELECT clie_nmcliente, regexp_replace(clie_nmcliente,'"','*','g')
FROM cadastro.cliente
WHERE clie_nmcliente like '%"%';

this function could solve your problem, only have to change the name
of each table ...

CREATE OR REPLACE FUNCTION replace_char(oldcharacter character
varying, newcharacter character varying)
RETURNS void AS
$BODY$
DECLARE
rowtable RECORD;
BEGIN
FOR rowtable IN SELECT * FROM cadastro.cliente LOOP
UPDATE cadastro.cliente set clie_nmcliente =
regexp_replace(clie_nmcliente,oldcharacter,newcharacter,'g');
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';

I run this query to change the text " by * :

SELECT replace_char( '"' , '*' );

should read the documentation:

http://www.postgresql.org/docs/current/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Saludos y abrazos...

Marco Antonio Frias Butrón
marcofrias(at)linuxpackages(dot)net
Slackware ~ Linux User #356229