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:


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


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.

  • 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? Commented Nov 19, 2013 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
    Commented Nov 19, 2013 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
    Commented Nov 19, 2013 at 17:41

1 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:


So this also works:


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:


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

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

  • Good answer but what happens when my field is '^' or '-' or '.' ?
    – Matthew
    Commented Nov 19, 2013 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... Commented Nov 19, 2013 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
    Commented Nov 19, 2013 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. Commented Nov 19, 2013 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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