Getting 'Error converting data type varchar to numeric.'

Question

I have a table called testTable with two columns, id that is auto incremented and someValue.

The data contained in the someValue column are: 12, 1.2, .4, 1d4, +, -, .

Data type for someValue is varchar(50).

Why are the following queries throwing

Error converting data type varchar to numeric.

select ID, someValue 
from testTable 
where ISNUMERIC(someValue + 'd0') = 1 and CAST(someValue as decimal(8,2)) > 0.1;

select tt.ID,tt.someValue 
from (select ID, someValue 
      from testTable 
      where ISNUMERIC(someValue + 'd0') = 1) as tt 
where CAST(tt.someValue as decimal(8,2)) > 0.1;

Your first statement will fail since SQL Server never guarantees to evaluate the first part of the `WHERE` clause before the second ... — marc_s, Jan 28 '13 at 17:07
possible duplicate of [TSQL divide by zero encountered despite no columns containing 0](http://stackoverflow.com/questions/5191701/tsql-divide-by-zero-encountered-despite-no-columns-containing-0). You would need to put the cast inside a case expression. — Martin Smith, Jan 28 '13 at 17:38
Your second query is failing because SQL Server never guarantees to evaluate subselects before the select. How about telling us **why** you are trying to compare things like '1d4' and '+' as decimal? What is the point? — Dour High Arch, Jan 28 '13 at 17:38
I want to get numerical values greater that a number say 1. But the problem is the column contains both numerical and non-numerical values. — Harke, Jan 28 '13 at 19:22

score 1 · Answer 1 · answered Jan 29 '13 at 01:15

You have a few problems; CAST does not work on non-decimal input, and ISNUMERIC accepts strings that can convert to monetary amounts, including non-decimal values like '-', '.', or 'US$100'.

The right way to solve this is to add a Decimal column to your database, have whatever populates someValue to populate the Decimal column with the value you want to compare against, and only compare against the Decimal column.

If, for some reason, you cannot do that, you can use ISNUMERIC and only include non-monetary decimal amounts, and use Convert instead of CAST:

select ID, someValue 
from testTable 
where ID IN
    (select ID from testTable where ISNUMERIC(someValue) = 1
    AND Patindex('%[^0-9-+.]%', someValue) = 0
    AND someValue NOT IN ('-', '+', '.')
    )
    and Convert(decimal, someValue) > 0.1

score 0 · Answer 2 · answered Jan 28 '13 at 16:59

0

In your first statement you take directly from testTable and attempt:

CAST(someValue as  decimal(8,2))

This will throw an error as you have not filtered out non-numerical values.

answered Jan 28 '13 at 16:59

Curtis

101,612
66
270
352

Thanks. But what about the second query? I got a table tt with numeric values in varchar. But that is also giving me error. – Harke Jan 28 '13 at 17:18

Getting 'Error converting data type varchar to numeric.'

2 Answers2