codelover codelover - 3 months ago 8
SQL Question

Failed Convert Varchar to a Float

I have a table that has got a mixture of data, its of type Varchar but i has got numbers and a few strings in it which are

Building
and
IBS
. However i'm trying to get rid of the strings. I only know how to get rid of one. When i try to add another, It complains about arguments, how can i do it.

ROUND( ISNULL( CASE
WHEN [lentgth] IN( 'Building', '', 'IBS', '') THEN 0
ELSE CONVERT(FLOAT, REPLACE([lentgth], 'm', 'i'))
END, 0 ), 0 )

Answer

You can use isnumeric function:

ROUND(cast(case
            when isnumeric(lentgth) = 0 then 0 
            else lentgth
           end as float)
     , 0)