Need to update a column values into NULL if the value contains a non numeric values.

Database

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.

3 REPLIES
Junior Contributor

Re: Need to update a column values into NULL if the value contains a non numeric values.

TD14 supports TO_NUMBER which returns NULL if the conversion fails:

select to_number(col)

Re: Need to update a column values into NULL if the value contains a non numeric values.

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 ;

Enthusiast

Re: Need to update a column values into NULL if the value contains a non numeric values.

UPDATE TABLENAME SET COL1=NULL WHERE REGEXP_SIMILAR(TRIM(COL1),'^[0-9]+') <> 1

Can you try this?

Cheers,

Mani