Update wrong values

[tora34]

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

[madmax]

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:

[madmax]

Follow an example:

Select all

update enderecos_clientes  
SET REPLACE(REPLACE(replace(rg,',',''),'.',''),'/','')... e assim vai    
where... 

Madmax.

[tora34]

But then I will have to use 20 replaces, one followed by the other.
Is it only that way?

[SergioLBJr]

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

[madmax]

Sergio is right !!!

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

ABS.

Madmax.

[nozes]

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

[gpilger]

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]

Translate has to work. Look at an example:

Select all

upper(rpad(nvl(translate(ad.ADDRESS4,'ãÃõÕçÇüÜâÂêÊôÔáÁàÀéÉíÍóÓúÚ!@#$%&*()_+=[]{}/\?:<>|' 
,'aAoOcCuUaAeEoOaAaAeEiIoOuU'), ' '), 12))

[fsitja]

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