Snowflake - Convert varchar to numeric

Question

I have a column(field1) defined as varchar in snowflake. It is storing both string and numbers(ex values: US15876, 1.106336965E9). How can I convert the numeric values to display something like 1106336965, without losing the columns that is storing string values or null values. I am trying try_to_numeric(field1), but this is eliminating the record with string values and showing them as null. Any help is appreciated.

Answer 1

So try_to_number is the way to have numbers, and nulls for non-number without errors. But if you want to keep the strings, you actually have to convert your newly create number, back to text (or variant), otherwise it cannot be in the same column, so nothing is gained:

select column1
    ,try_to_number(column1) as_num
    ,nvl(as_num::text, column1) as why_not_both
from values
  ('US15876'), 
  ('1.106336965E9'),
  ('1.106336965'),
  ('1106336965');

COLUMN1	AS_NUM	WHY_NOT_BOTH
US15876	null	US15876
1106336965	1,106,336,965	1106336965
1.106336965	1	1
1106336965	1,106,336,965	1106336965