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
Can you please suggest how to do this.
TD14 supports TO_NUMBER which returns NULL if the conversion fails:
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 ;
UPDATE TABLENAME SET COL1=NULL WHERE REGEXP_SIMILAR(TRIM(COL1),'^[0-9]+') <> 1
Can you try this?