On Fri, Nov 14, 2008 at 12:17 PM, paulo matadr <saddoness@yahoo.com.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@linuxpackages.net
Slackware ~ Linux User #356229
