Thread: Enc: Help to replace caracter

Enc: Help to replace caracter

From
paulo matadr
Date:



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



Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 - Celebridades - Música - Esportes

Re: Enc: Help to replace caracter

From
Raymond O'Donnell
Date:
On 14/11/2008 16:17, paulo matadr wrote:


> Any help me to create pgPL/sql or funcion  to replace ( " )  to null
> value, I have many table with this.

Do you mean you want to replace just those characters with NULL? Won't
this cause the entire string to be NULL?

postgres=# select 'abc' || NULL;
 ?column?
----------

(1 row)


Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: Enc: Help to replace caracter

From
"Marco Antonio"
Date:
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