ISNUMERIC issue: Conversion failed when converting the nvarchar value to data type int

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 

FROM ...

WHERE ISNUMERIC(MyColumn) = 1

Error:

cast failed: Conversion failed when converting the nvarchar value to data type int

But..... we check that ISNUMERIC(MyColumn) = 1, so why it is failed?

Cause:

Some charactes that are not numbers are return from ISNUMERIC as numeric, for example:

select

       ISNUMERIC('-'),

       ISNUMERIC('+'),

       ISNUMERIC('$'),

       ISNUMERIC('.'),

       ISNUMERIC(','),

       ISNUMERIC('\')

will return 1 to all!

Solution: TRY_PARSE

in the conditions, check WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 

FROM ...

WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

TRY_PARSE tries to parse:

• if it can parse - it return the pared value,
• if not - it return NULL, and not fail the query!!!

so, if we check thet TRY_PARSE IS  NOT NULL, we can do the casting in the select.