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
Raj Mathur
Date:
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
D0EFCC68 D17F
 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves