Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

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

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

http://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.

share|improve this question
1  
So do you want 10,1 included in the calculation as 10.1, or do you want it excluded? Do you only want integers to be considered? Are you going to fix the schema? –  Aaron Bertrand Nov 19 '13 at 17:37
    
@AaronBertrand I want 10,1 excluded. In fact, I want all data points excluded which will cause SUM to fail without being manipulated. If there was a regional setting enabled which permitted 10,1 to be a valid parameter for SUM then I would want it included. –  Matthew Nov 19 '13 at 17:38
5  
I ran into a similar situation and Aaron's suggestion led me to staging valid data into a temporary table and then querying from there since I could not ensure the optimizer would approach it the same every time –  billinkc Nov 19 '13 at 17:41
add comment

1 Answer

up vote 4 down vote accepted

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"...

share|improve this answer
    
Good answer but what happens when my field is '^' or '-' or '.' ? –  Matthew Nov 19 '13 at 17:55
3  
@Matthew did you try them? They don't break my example (I added all three values). Now, something else like 2.4.5 will (and some of those values may have a different impact on calculations like AVG because they may pass the filter but are treated as 0). But I still go back to the end of my answer: use the right data type, and this data won't get in there in the first place. If that's not possible, then at least try to prevent bad data from getting into your wrong data type. If that's not possible, extend the filter to cover other bad data... –  Aaron Bertrand Nov 19 '13 at 18:05
    
it now works with '.' and '-' since you updated to CONVERT(MONEY,n) from CONVERT(DECIMAL In the case of 2.4.5 I can still use ISNUMERIC so, perhaps, a combination of the two is a good answer here –  Matthew Nov 19 '13 at 18:12
1  
@Matthew sorry, I did not mean to change the destination type silently. I started with MONEY, copied the code from my VM, and changed it to DECIMAL afterward. When I added the additional input values the script still had MONEY, I copied and pasted and brought it back. –  Aaron Bertrand Nov 19 '13 at 18:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.