Search code examples
sqlsql-serverdatabaset-sqlisnumeric

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 )
Solution

  • You can use isnumeric function:

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