Cannot convert Varchar to Numeric issue

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,

MichelMichel (Michel@.askme.com) writes:
> 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).

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
http://www.microsoft.com/sql/techin.../2000/books.asp