All Forums Database
09 Jun 2015
Need to update a column values into NULL if the value contains a non numeric values.

Hi Team,
I have a situation like the column is defined as char and it contain non numeric values as well. I wanted to update those values into NULL. 
EX. My column contains below data
a123h
1fhh
123
hdh2hfh4
Requred output:
NULL
NULL
123
NULL
Can you please suggest how to do this.

dnoeth 4628 posts Joined 11/04
09 Jun 2015

TD14 supports TO_NUMBER which returns NULL if the conversion fails:
select to_number(col)

Dieter

10 Jun 2015

Hi Dhoeth,
Thanks for writing. 
I tried TO_NUMBER , but it returned values like <error> instead of null. Then, I tried below function and is working fine.

UPDATE TABLENAME SET COL1=NULL WHERE REGEXP_REPLACE(COL1,[A-Za-z]*','',1,0,'i') <> COL1 ;
 

 

manib0907 61 posts Joined 04/15
10 Jun 2015

UPDATE TABLENAME SET COL1=NULL WHERE REGEXP_SIMILAR(TRIM(COL1),'^[0-9]+') <> 1
Can you try this?
Cheers,
Mani

Cheers,
Mani

You must sign in to leave a comment.