mssql convert varchar to float

Question

I have a field value productlength of 0 123  This is from a view and has a data type of varchar   I need to convert it to a float or numeric value so as o perform math comparisons   convert float productlength  and  cast productlength as float  both do not work   error varchar cant be converted to float or somethiing liek that   From what I have read varchar can simply not be converted to a numeric string   Any clever ways around this

User · Answer

Use   Try convert float  Value     See  https   raresql com 2013 04 26 sql-server-how-to-convert-varchar-to-float

User · Answer

You can convert varchars to floats  and you can do it in the manner you have expressed   Your varchar must not be a numeric value   There must be something else in it   You can use IsNumeric to test it   See this   declare  thing varchar 100   select  thing    122 332   --This returns 1 since it is numeric  select isnumeric  thing   --This converts just fine  select convert float  thing   select  thing    122 332    --This returns 0 since it is not numeric  select isnumeric  thing   --This convert throws  select convert float  thing

User · Answer

DECLARE  INPUT VARCHAR 5     0 12   INPUT 1 VARCHAR 5   0 12x   select CONVERT float   INPUT  YOUR QUERY   case when isnumeric  INPUT 1  1 THEN CONVERT float   INPUT 1  ELSE 0 END AS YOUR QUERY ANSWERED   above will return values  however below query wont work   DECLARE  INPUT VARCHAR 5     0 12   INPUT 1 VARCHAR 5   0 12x   select CONVERT float   INPUT  YOUR QUERY   case when isnumeric  INPUT 1  1 THEN CONVERT float   INPUT 1  ELSE    INPUT 1   END AS YOUR QUERY ANSWERED   as  INPUT 1 actually has varchar in it   So your output column must have a varchar in it

[sql-server] mssql convert varchar to float

Examples related to sql-server

Examples related to type-conversion