CODEWITHSUNDEEP
sql-serversql-server-2008t-sqltype-conversion
diiN__________
diiN__________

Reputation: 7656

Using ISNUMERIC fails in condition

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.

Upvotes: 0

Views: 1143

Answers (2)

David Rushton
David Rushton

Reputation: 5030

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
;

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 3

Related Questions