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