Error converting data type varchar to float. - Try_Cast() - Try_Parse() - Try_convert()

Error was happening when SQL server is trying to join a float column with varchar and it tries to convert varchar to float explicitly.

I tried to handle it using IsNumeric function but there was '.' dot present in my data set which lead to above error.

SELECT 
 ISNUMERIC('123') as '123'
 ,ISNUMERIC('.') as '.' --Period
 ,ISNUMERIC(',') as ',' --Comma

Function ISNUMERIC() returns "1" when the input expression evaluates to a valid numeric data type; otherwise it returns "0". But the above query will return value "1" for all 3 column values, validating them as numeric values, but that's not correct for last 2 columns.

And not only this, ISNUMERIC() function treats few more characters as numeric, like: - (minus), + (plus), $ (dollar), \ (back slash), check this:

SELECT 
 ISNUMERIC('123') as '123'
 ,ISNUMERIC('abc') as 'abc'
 ,ISNUMERIC('-') as '-'
 ,ISNUMERIC('+') as '+'
 ,ISNUMERIC('$') as '$'
 ,ISNUMERIC('.') as '.'
 ,ISNUMERIC(',') as ','
 ,ISNUMERIC('\') as '\'

This will return "0" for second column containing value "abc", and value "1" for rest of the column values.

So, you will need to be very careful while using ISNUMERIC() function and have to consider all these possible validations on your T-SQL logic.

- OR -

Switch to new TRY_PARSE()  or TRY_CAST() or TRY_CONVERT() function introduced in SQL Server 2012.

above behavior of isNumeric function was causing the above error.

handled the logic using 

=> The TRY_PARSE() function returns the result of an expression, translated to the requested Data-Type, or NULL if the Cast fails. Let's check how TRY_PARSE() validates above character values as numeric:

SELECT 
 TRY_PARSE('123' as int) as '123'
 ,TRY_PARSE('abc' as int) as 'abc'
 ,TRY_PARSE('-' as int) as '-'
 ,TRY_PARSE('+' as int) as '+'
 ,TRY_PARSE('$' as int) as '$'
 ,TRY_PARSE('.' as int) as '.'
 ,TRY_PARSE(',' as int) as ','
 ,TRY_PARSE('\' as int) as '\'

or

TRY_CONVERT(INT,Number)
or

references

https://blogs.msdn.microsoft.com/manub22/2013/12/23/use-new-try_parse-instead-of-isnumeric-sql-server-2012/