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.