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.

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

Solution 2

Check this out:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Basically, there are some known issues / oddities with the ISNUMERIC function. The author of that article suggests creating a new function and using that instead of ISNUMERIC. I tested it with the 1,0 value you suggested and it seems to work.

CREATE FUNCTION dbo.isReallyNumeric  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    DECLARE @pos TINYINT  

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
        AND  
        (  
            ((@pos = LEN(@num)+1)  
            OR @pos = CHARINDEX('.', @num))  
        )  
    THEN  
        1  
    ELSE  
    0  
    END  
END  
GO 

Solution 3

Simply an explicit conversion but with a little ingenuity the REPLACE

SELECT result FROM table WHERE CONVERT(float,REPLACE(result,',','.')) > 180.0

Comments

[Remus Rusanu]

Can you give the exact syntax you use? Your WITH .. AS (CASE ... ) is incomplete and incorrect.

[Jeremy Wiggins]

I tried to reproduce the error but couldn't... I put the following before the code you posted and selected from @t and got back 199. declare @t table ( result varchar(500) ) insert into @t (result) values ('199.00') insert into @t (result) values ('test');

[Remus Rusanu]

@Jeremy: you need a test value that passes the ISNUMERIC test but fails to convert, like '1,0'.

[chris]

Unfortunately I cannot change the design of the database :( Is there a common idiom for getting SQL Server to spit out which rows it can't CAST / CONVERT ?

[Remus Rusanu]

No, not really. See simple-talk.com/community/blogs/philfactor/archive/2011/01/1‌​3/…

[Vic Colborn]

This special case where IsNumeric issatified but a record like cast(result) fails because of the comma are unfortunately quite common. You can remove the comma with a statement like REPLACE(result,',',''). Better to fix it upstream if at all possible though.

[prospector]

that worked great, it found a negative number that was stopping a float cast