SQL case statment not working for ISNUMERIC check

create table #temp ( strAmount varchar(50))
  insert into #temp values('123A')
  insert into #temp values('123.00')
  select * from #temp

  select
 CASE
    When ISNUMERIC(strAmount)=1 then
       CONVERT(varchar(50),CONVERT(Numeric(10,2),strAmount))
    else
       strAmount
  End
 from #temp

As you are inserting monetary value in the table,
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types are

int

numeric

bigint

money

smallint

smallmoney

tinyint

float

decimal

real

Ref: https://msdn.microsoft.com/en-us/library/ms186272.aspx[^]



so isnumeric returns false for the first item and true for the second.

When it is true, you are trying to convert it to number whereas the actual value is not really a number as it contains '$' char. so the conversion fails and you will get error.


Try the following query

select
CASE
   When ISNUMERIC(strAmount)=1 then
      CONVERT(varchar(50),CONVERT(numeric(18,2),CONVERT(Money,strAmount)))
   else
      strAmount
 End
from #temp

Here first we are converting the data to money and then to a required number format.

Another thing to note here is you need to convert the resulting number to varchar because we are returning the non-numeric values also in this query. so you need to use
CONVERT(varchar(50),CONVERT(numeric(18,2),CONVERT(Money,strAmount)))
if you use
CONVERT(numeric(18,2),CONVERT(Money,strAmount))

again you will get error because the query returns different data types...i.e. if it is numeric, it returns a number else varchar. so you need to convert the resulting number to varchar.

Hope I'm clear.