>>>>1e5 or 1d4 should be treated as numeric by isnumeric function. In fact, Sergey has a blog on this very topic
>>>>http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-aware
>>>
>>>This is a sophisticated technique. Thanks for the reference
>>>
>>>However, this one also uses the WHERE approach. Thus, all records not eligible will not be taken into consideration. I am looking at including all records but to convert the non numeric values from the character field into 0, when this is not something that SQL Server could convert. I do not know if that is possible.
>>
>>It is possible. Simply use CASE statement, e.g.
>>

>> select case when MyColumnName NOT LIKE '%[^0-9]%' then 0 else convert(int, MyColumnName) END as Converted
>>

>>
>>Also, in SQL 2012 you may want to look at TRY_CONVERT function.
>
>You miss the decimal point:
>

>SELECT CASE WHEN '1.0' LIKE '%[^0-9]%'  THEN 'not number' ELSE 'number' END
>

I've been thinking about integer numbers.