Update wrong values

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
tora34
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 108
Joined: Wed, 12 Nov 2008 6:01 pm
Location: Campo Mourão PR

Good morning guys, Next:
In a particular table has a column created to register documents, RG, Varchar2 type. Only values ??with points (.) Virgula, letters, garbage.
I want to make an update to leave only the numbers.
I created a routine using Translate, but it gets crazy and updates fields by placing letters on them.
What to do?

follows code:

Select all

update enderecos_clientes 
SET RG=translate(upper(rg),'ABCDEFGIJLMNOPRSTU,./-;:\_',' ')  
where ...
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:

Face
Try to use the Replace.

Select all

select replace('23.683-567','-','') from dual
The result is = 23.683567

Try to update Thus:

Select all

Update REPLACE(replace(CAMPO,'-',''),'.','') from SUA_TABELA
I hope I have helped.

Douglas - Madmax: Wink:
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:

Follow an example:

Select all

update enderecos_clientes  
SET REPLACE(REPLACE(replace(rg,',',''),'.',''),'/','')... e assim vai    
where... 
Madmax.
tora34
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 108
Joined: Wed, 12 Nov 2008 6:01 pm
Location: Campo Mourão PR

But then I will have to use 20 replaces, one followed by the other.
Is it only that way?
SergioLBJr
Rank: Oracle Guru
Rank: Oracle Guru
Posts: 448
Joined: Tue, 16 Jun 2009 3:07 pm
Location: Parobé - RS
Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

Dude looks well this translate, I tested here and it inserts the letters that you did not pass to him

H, K, V, X, Y, W, Z
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:

Sergio is right !!!

And on the site here talks about Translate.
http://en.glufke.net/oracle/viewtopic.php?t=1397

ABS.

Madmax.
nozes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Thu, 23 Dec 2010 2:06 pm
Location: Mesquita-RJ
Emanuel "Nozes" Castro
Analista Programador

Hey guys ...
You can use this translate at the time of upgrading your field .... if you do not fully meet your need, check the field arguments to include or exclude same values.

Big hug and I hope I have helped .. Although the small delay of 4 years ... hehehe
;)

C Yah: - O
User avatar
gpilger
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 21 Apr 2010 3:34 pm
Location: Novo Hamburgo - RS
Gilson Pilger
"Por não saber que erra impossível, ele foi lá e fez" autor desconhecido

I already used the two, translate and the replace ... the ideal is to use the translate, after all it was made for this ... abs
marlonpasquali
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 248
Joined: Fri, 06 Feb 2009 3:02 pm
Location: ERECHIM - RS

Translate has to work. Look at an example:

Select all

upper(rpad(nvl(translate(ad.ADDRESS4,'ãÃõÕçÇüÜâÂêÊôÔáÁàÀéÉíÍóÓúÚ!@#$%&*()_+=[]{}/\?:<>|' 
,'aAoOcCuUaAeEoOaAaAeEiIoOuU'), ' '), 12))
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Try with regular expressions, it is much more succinct:

Select all

 
select regexp_replace(str, '[^0-9]') from t; 
Example:

Select all

 
SQL> with t as ( 
  2  select 'FG2;L4#4*$¨#H888G&*99@#+00.B^/?"1234{' str from dual) 
  3  -- fim dos dados de teste 
  4  select regexp_replace(str, '[^0-9]') from t; 
  
REGEXP_REPLACE(STR,'[^0-9]') 
---------------------------- 
24488899001234 
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 2 guests