CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
Harke
Harke

Reputation: 1289

Getting 'Error converting data type varchar to numeric.'

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;

Upvotes: 0

Views: 19373

Answers (2)

Dour High Arch
Dour High Arch

Reputation: 21712

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

Upvotes: 1

Curtis
Curtis

Reputation: 103368

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.

Upvotes: 0

Related Questions