Cannot convert varchar to float

Question

I get the error:

Cannot convert datatype varchar to float

for the following sql statement:

SELECT
    a, b,
    CASE 
       WHEN ISNUMERIC(c) = 1 
          THEN (CASE WHEN c LIKE '%.%' 
                       THEN CAST(c AS FLOAT) 
                       ELSE c + '.00' END) 
          ELSE '0.00' END
FROM
    table_name
WHERE
    b = 17

Please help.

Here c is a column of datatype varchar(50)

I can't declare it as a float datatype either as I need to store some varchar values in it.

Are you sure that you are using mysql? `ISNUMERIC()` does not exist in mysql. I think you use MS SQL Server. Right? — Roman Marusyk, Aug 25 '15 at 15:18
Your select is proper. I think there is problem with value that return from CASE. Where do you use this value? — Roman Marusyk, Aug 25 '15 at 15:32

Gordon Linoff · Accepted Answer · 2015-08-25T22:51:27.667

2

This code should work for reasonable values of c:

Select (CASE WHEN ISNUMERIC(c) = 1
             THEN (CASE WHEN c LIKE '%.%' THEN c ELSE c + '.00' END)
             ELSE '0.00'
        END)
from table_name
where b = 17;

Hence, you must have an unreasonable value. My guess is that you have something like '3e4', which is in exponential notation. Then you would get an error when converting '3e40.00'.

Your code itself doesn't make much sense. The value being returned from the case is a float. So, just do:

Select (CASE WHEN ISNUMERIC(c) = 1
             THEN CAST(c as FLOAT)
             ELSE 0.00
        END)
from table_name
where b = 17;

This is functionally equivalent and much simpler.

edited Aug 25 '15 at 22:51

answered Aug 25 '15 at 15:39

Gordon Linoff

1,242,037
58
646
786

I need that that +.00 part anyway so i cannot remove it.it doesnot have any exponential values either.i checked. – Linta Sheelkumar Aug 25 '15 at 15:50
Could you tell me some other reason of why this could happen? – Linta Sheelkumar Aug 25 '15 at 15:50
@LintaSheelkumar . . . Why do you need the `+ '0.00'`? It has no effect on the return value of the case expression. The return value is a float anyway and the zeros have no significance. – Gordon Linoff Aug 25 '15 at 15:54
@LintaSheelkumar . . . Perhaps the error is in some other part of the code. As written in the answer, the `SELECT` should not generate that error. Nor should the `where` (the error would involve an `int` and not a `float` if `b` is a string). – Gordon Linoff Aug 25 '15 at 22:52

score 0 · Answer 2 · edited May 23 '17 at 10:27

Assuming you haven't fallen prey to any of the quirks in ISNUMERIC() like ISNUMERIC(','), I think you've fallen into the same problem described here. As that poster so eloquently put it, "order of declaration does not imply order of execution".

In other words, just because you say

CASE WHEN ISNUMERIC(c) = 1 
    THEN (CASE WHEN c LIKE '%.%' 
        THEN CAST(c AS FLOAT) 
        ELSE c + '.00' END) 
ELSE '0.00' END

does not mean that ISNUMERIC(c) = 1 will control how CAST(c AS FLOAT) is executed. The only way to control execution order is to use a different clause entirely. In this case, you should use the WHERE clause and UNION ALL the results.

Also, note that CASE WHEN c LIKE '%.%' THEN CAST(c AS FLOAT) ELSE c + '.00' END is functionally identical to just saying CAST(c AS FLOAT) because only one data type is allowed per column.

That means you need to do this:

SELECT a,
    b,
    CAST(0.00 AS FLOAT)
FROM table_name
WHERE b = 17
    AND ISNUMERIC(c) <> 1

UNION ALL

SELECT a,
    b,
    CAST(c AS FLOAT)
FROM table_name
WHERE b = 17
    AND ISNUMERIC(c) = 1

Beyond that, consider if you really want FLOAT. It's not a precise data type, and it's fairly uncommon that precision is not a requirement of an RDBMS. If you need precision, use NUMERIC or DECIMAL.

Cannot convert varchar to float

2 Answers2