sql server - error converting varchar to float

Question

Getting an error converting varchar to float.

Have a table (not of my making) with column result, having varchar data. I'd like to convert to float to grab all values > 180.0.

SELECT result FROM table WHERE result > 180.0

Produces the error. Interestingly, though:

WITH temp AS (
  CASE
    WHEN ISNUMERIC(result)=1 THEN CAST(result as FLOAT)
    ELSE CAST(-1.0 AS FLOAT)
  END AS result
)

This runs fine. When I pair it with:

SELECT temp.result FROM temp WHERE temp.result > 180.0

I get the error again. Thoughts?

UPDATE: Full code Requested...

WITH temp AS (
  SELECT
  CASE
    WHEN ISNUMERIC(result)=1 THEN CAST(result as FLOAT)
    ELSE CAST(-1.0 AS FLOAT)
  END AS result
  FROM table)

SELECT temp.result FROM temp WHERE temp.result > 180.0

Using SQL-SERVER 2008 RC2.

Answer 1

It means you have at least one row in the table that cannot be cast to float. Doing the CASE is safe, but combining the CTE and adding a WHERE clause falls into a common fallacy of programmers when writing T-SQL: that order of declaration implies an order of execution. Programmers are used to the imperative procedural style of C like languages and fail to comprehend the declarative set based nature of SQL. I have wrote before about this issue and gave examples when the fallacy causes errors:

• T-SQL functions do no imply a certain order of execution
• On SQL Server boolean operator short-circuit

Once you post your full code we can see where exactly did you make the fallacy in your case and assumed a certain order of execution.

after update

OK, so I have to admin that in your case the code is correct in the order of execution, the result column cannot be projected w/o first evaluating the CASE. Had the CASE been in a WHERE clause things would have been different.

Your problem is different: ISNUMERIC. This function has a very generous understanding of what NUMERIC means and has bitten many developers before. Namely, it accepts values that CAST and CONVERT will reject. Like ones containing a comma:

declare @n varchar(8000) = '1,000';
select isnumeric(@n);
select cast(@n as float);
select case when isnumeric(@n)=1 then cast(@n as float) else null end;

So you have values that pass the ISNUMERIC test but fail to convert. Just a heads up, the more you'll digg into this approach, the more closed doors you'll find. Is just no safe way to do the cast you need on the server side. Ideally, fix the data model (make the field a float if it stores floats). Short of that, triage the data and remove all values that are not a proper float, and fix the front-end/application to no longer introduce new ones, then add a constraint that will trigger the error if new bad values appear. You won't be able to solve this in a query, that road is littered with bodies.

With the next version of SQL Server you will have a new function, TRY_CONVERT, that would solve your problem.