Using ISNUMERIC fails in condition

Question

I have a table like this (simplified):

CREATE TABLE #table (Id INT, Field NVARCHAR(MAX))
INSERT INTO #table VALUES (1, 'SomeText')
INSERT INTO #table VALUES (2, '1234')

For some reasons I need to query this table and get the sum of Field if it is numeric and return '' if it is not. I tried it like this:

SELECT CASE WHEN ISNUMERIC(Field) = 1 THEN SUM(CONVERT(MONEY, Field)) ELSE '' END
FROM #table
GROUP BY Field

But this query leads to the following exception:

Cannot convert a char value to money. The char value has incorrect syntax.

I even changed the ELSE case from '' to 0 but I still get the same message.

Why do I get the exception? As far as I know, SUM(...) should not be executed when ISNUMERIC(Field) returns 0.

this would fail anyway, since a CASE expression can return just one datatype. In your case, you are using SUM on a MONEY datatype (which returns MONEY), or '' which is obviously a string — Lamak
– Lamak, Commented Dec 12, 2016 at 14:31
@Lamak that's why I changed '' to 0 to check if this would help. However, the message still appears. — diiN__________
– diiN__________, Commented Dec 12, 2016 at 14:32
because you should use the CASE expression inside the SUM for starters: SUM(CASE WHEN ISNUMERIC(Field) = 1 THEN Field END) — Lamak
– Lamak, Commented Dec 12, 2016 at 14:33
That's a point. But why does the error appear when I leave it outside SUM? — diiN__________
– diiN__________, Commented Dec 12, 2016 at 14:40
Although you have already accepted an answer, i'd caution you against using ISNUMERIC for most cases similar to this. Are the only potential values varchar and int? — S3S
– S3S, Commented Dec 12, 2016 at 14:48

John Cappelletti · Accepted Answer · 2016-12-12 14:33:44Z

3

Select sum(case when  ISNUMERIC(Field)=1 then cast(field as money) else 0 end)
 from #table
 Group By Field

Returns

(No column name)
1234.00
0.00

answered Dec 12, 2016 at 14:33

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

diiN__________ Over a year ago

Thanks, it works now :) the only thing I don't understand is why I only get the exception when I leave the CASE statement outside SUM.

John Cappelletti Over a year ago

@diiN_ Happy to help. With the case outside of the sum, you will get a data type confict. Inside, we normalize the data type.

David Rushton · Accepted Answer · 2016-12-12 14:38:37Z

0

Working with mixed datatypes can be a real pain. Where possible, consider table designs that avoid this. To further complicate matters, IsNumeric does not always return what you might expect.

Filtering out the non-numerics before aggregating is one way to go:

SELECT 
    SUM(CONVERT(MONEY, Field)) 
FROM 
    #table
WHERE
    ISNUMERIC(Field) = 1
GROUP BY 
    Field
;

edited Dec 12, 2016 at 14:38

answered Dec 12, 2016 at 14:33

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

2 Comments

diiN__________ Over a year ago

The problem is that I also need a value (for example 0) for those fields that aren't numeric.

David Rushton Over a year ago

@JohnCappelletti provides a better solution than my own. This will do what you want.

Collectives™ on Stack Overflow

Using ISNUMERIC fails in condition

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related