>>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.


Use CASE :

SELECT ...
       CASE WHEN ISNUMERIC(YourField+'e0') = 1
                 THEN CAST(YourField as Numeric(xx, yy)
            ELSE 0 END AS YourNumberField