Reporting a IsNumric function Bug

Question

I believe its definitely a bug of IsNumeric function, it shows all 2 values are valid numeric and returns 1, however say when you try to convert gives conversion fail error

Declare @TestNumeric TABLE

(
Id int ,

Amount varchar(10)

)

Insert into @TestNumeric values (1,'6,923.47')

Insert into @TestNumeric values (2,'6923.47')

Select ISNUMERIC(Amount) from @TestNumeric --This will show all values are valid Numeric

Select cast(Amount as numeric(10,2)) from @TestNumeric --This will fail to convert into Numeric as oppose to above

Answer 1

Hi @Syed Rashid Nizam

This has been discussed before, you can take a look at this thread.

https://stackoverflow.com/questions/4603292/check-if-a-varchar-is-a-number-t-sql

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

No, it is not a bug. It's only a useless design. Isnumeric retuns 1 if the value can be converted to any numeric data type. Look at this:

Declare @TestNumeric TABLE (Id int ,                   
                            Amount varchar(10)
)

Insert into @TestNumeric values (1,'6,923.47')
Insert into @TestNumeric values (2,'6923.47')

Select ISNUMERIC(Amount) AS IsNumeric, 
       try_cast(Amount AS numeric(20,2)) AS "numeric/decimal",
       try_cast(Amount AS money) AS money
FROM   @TestNumeric

Stay away from isnumeric. As the example suggests, use try_cast/try_convert instead.