Convert varchar to flaot with multiple CASE – SQL

July 13, 2022

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');

>Solution :

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