I have a dataset with number (some negative) as varchar in my column, some cell also contain '#N/A', '#DIV/0!' and 'Null'. I'm trying to convert it to float.
But when I run my code it convert everything to 'Null'
Here is my code:
Select col1,
case
when col1 = '#DIV/0!' then null
when col1 = '#N/A' then null
else TRY_CAST ( col1 as float)
end as col2
from test
And a dataset sample:
CREATE or replace table Test (
Col1 VARCHAR(30));
INSERT INTO silver_db.public.test
(Col1)
VALUES
('#DIV/0!'),
('#N/A'),
('5 554 548'),
('-230 896'),
('Null');
CodePudding user response:
It will not know what to do with the numbers with spaces in it. You need to replace these:
Select col1,
case
when col1 = '#DIV/0!' then null
when col1 = '#N/A' then null
else TRY_CAST(REPLACE(col1, ' ' ,'') as float)
end as col2
from test