Error converting data type nvarchar to numeric - SQL Server

Question

I am trying to take an average of a column in my database. The column is AMOUNT and it is stored as NVARCHAR(300),null.

When I try to convert it to a numeric value I get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting datatype NVARCHAR to NUMBER

Here is what I have right now.

SELECT AVG(CAST(Reimbursement AS DECIMAL(18,2)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 
  AND Reimbursement IS NOT NULL

Answer 1

You would think that your code would work. However, SQL Server does not guarantee that the WHERE clause filters the database before the conversion for the SELECT takes place. In my opinion this is a bug. In Microsoft's opinion, this is an optimization feature.

Hence, your WHERE is not guaranteed to work. Even using a CTE doesn't fix the problem.

The best solution is TRY_CONVERT() available in SQL Server 2012+:

SELECT AVG(TRY_CONVERT(DECIMAL(18,2), Reimbursement)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL;

In earlier versions, you can use CASE. The CASE does guarantee the sequential ordering of the clauses, so:

SELECT AVG(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
                THEN CONVERT(DECIMAL(18,2), Reimbursement))
           END)
FROM Database;

Because AVG() ignores NULL values, the WHERE is not necessary, but you can include it if you like.

Finally, you could simplify your code by using a computed column:

alter database add Reimbursement_Value as
    (CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
          THEN CONVERT(DECIMAL(18,2), Reimbursement))
     END);

Then you could write the code as:

select avg(Reimbursement_Value)
from database
where Reimbursement_Value is not null;

Answer 2

Quote from MSDN...

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney

select isnumeric('+')---1
select isnumeric('$')---1

so try to add to avoid non numeric numbers messing with your ouput..

WHERE Reimbursement NOT LIKE '%[^0-9]%'

If you are on SQLServer 2012,you could try using TRY_Convert which outputs null for conversion failures..

SELECT AVG(try_convert( DECIMAL(18,2),Reimbursement))
from
table