Cannot convert Varchar to Numeric issue

[Michel]

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(DEC IMAL(16,8),F2)) MySum
FROM T1
WHERE IsNumeric(IsNul l(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

[Erland Sommarskog]

Re: Cannot convert Varchar to Numeric issue

Michel (Michel@askme.c om) writes:[color=blue]
> 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(DEC IMAL(16,8),F2)) MySum
> FROM T1
> WHERE IsNumeric(IsNul l(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).[/color]

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/techinfo/productdoc/2000/books.asp