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

On Friday 14 Nov 2008, paulo matadr 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

For a single field, you can use something like this to achieve your
objective (this will delete all " characters in the field):

update cadastro.cliente
set clie_nmcliente = regexp_replace(clie_nmcliente, '"', '', 'g')
where clie_nmcliente like '%"%'; -- delete all ": not tested!

You could replace the '' with some other string to replace all " with
that string.

It may be quicker to edit an ASCII dump of the database and reload it if
you want to do the same replacement in multiple fields in multiple
tables.

Regards,

-- Raju
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves