Thread: Enc: Help to replace caracter

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
------------------------------------------------------------------

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