sql server (alternate): Hi again all,

I have a small issue. Here's an example dataset :

F1 F2 F3
1 0.58 Hi
2 0.70 Hello
3 Fail Bye
4 <Null> Hi

When I write this statement :

SELECT SUM(CONVERT(DECIMAL(16,8),F2)) MySum
FROM T1
WHERE IsNumeric(IsNull(F2,'X'))=1

I get "Cannot convert a Varchar value to Numeric" error. From what I
understand, it somehow tries to convert to a decimal(16,8) BEFORE filtering
the nulls and the non-numeric out. (Keep in mind that the actual table has
over 1.5Million records).

Any idea on how to get around that ?

Thanks,

Michel

Michel (Michel@askme.com) writes:
[quoted text, click to view]

This might help:

SELECT SUM(CASE WHEN isnumeric(F2)
THEN convert(decimal(16, 8)), F2
ELSE 0
END) MySUM
FROM T1
WHERE isnumeric(F2) = 1

Beware that isnumeric may give you false positives. Not all strings
that causes isnumeric to return 1 are convertible to decimal.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at