What is the appropriate SQL method for "Safe to SUM"?

Question

In SQL Server 2008 R2+ we, unfortunately, have some nvarchar columns storing numeric data.

In some cases we want to SUM the data when it can be summed. The problem is that ISNUMERIC() returns 1 for a number of entries with are invalid parameters for SUM.... I understand what's going on here but I am looking for a workaround.

For example ISNUMERIC('10,1') = 1 but I cannot include that value in a SUM so I would like that value excluded from the SUM... and any data points, in fact, which cannot be parameters to SUM without being massaged...

So I'm wondering what is the preferred way to determine whether or not the contents of an alphanumeric column can be summed?

EDIT: It's worth noting that this is a take-off from this answer on SO:

https://stackoverflow.com/a/15833014/369775

but I am not convinced that the methods provided in that answer are optimal. I do agree with HLGEM:

https://stackoverflow.com/a/338292/369775

The best solution would be to stop storing integers in a varchar column. Clearly there is a data issue where the data is interpretable as a numeric but cannot be cast as such.

Answer 1

The challenge with relying on ISNUMERIC() for this is that it can look at a lot of data that can't be summed and give it a pass. It isn't IS_ELIGIBLE_FOR_SUM() - it is merely returning true if the input could be converted to any of the numeric types. Commas are allowed because conversion to MONEY, for example, works successfully:

SELECT CONVERT(MONEY,'11,2');

So this also works:

SELECT SUM(CONVERT(MONEY,'11,2'));

Which probably isn't what you want. I think it would be safer to replace commas with periods before attempting to perform operations, or just exclude them and filter out rows this way:

DECLARE @t TABLE(n NVARCHAR(32));

INSERT @t VALUES(N'11,2'),(N'32'),(N'-32.4'),(N'32323'),(N'^'),(N'.'),(N'-');

SELECT SUM(CASE WHEN ISNUMERIC(n) = 1
    AND n NOT LIKE '%[^0-9.-]%' THEN CONVERT(MONEY,n) END)
  FROM @t
  WHERE ISNUMERIC(n) = 1 AND n NOT LIKE '%[^0-9.-]%';

The reason you have to perform a CASE expression in addition to the filter is that you can't control how the optimizer will process the statement - without CASE it might try to perform the conversion before the filter.

Of course, the smartest course of action would be to stop using the wrong data type in the first place. If you continue storing dirty data in ill-advised data types, you don't really get to complain about "optimal"...