From: | "Marco Antonio" <marcoantoniofrias(at)gmail(dot)com> |
---|---|
To: | "paulo matadr" <saddoness(at)yahoo(dot)com(dot)br> |
Cc: | GENERAL <pgsql-general(at)postgresql(dot)org>, Sql-listas_post <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Enc: Help to replace caracter |
Date: | 2008-11-14 16:55:25 |
Message-ID: | 33e030de0811140855l7578bbb3sd4950e2b41b46635@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2008-11-14 18:05:24 | Re: Delete cascade trigger runs security definer |
Previous Message | Raymond O'Donnell | 2008-11-14 16:39:21 | Re: Enc: Help to replace caracter |
From | Date | Subject | |
---|---|---|---|
Next Message | sub3 | 2008-11-14 19:12:33 | Query to match location transitions |
Previous Message | Raymond O'Donnell | 2008-11-14 16:39:21 | Re: Enc: Help to replace caracter |