SQL Server converting a variable varchar field to money/decimal/something with decimal places

Question

I'm looking for an elegant way to convert a field of type varchar, with variable data in it, to a data type which can be used for mathematical operations sample data from the field (excluding quotes)

''
'abc'
'23'
'23.2'

The method should work for all, and for the first & second values should return 0, and not throw an SQL Server error..

Answer 1

SELECT  CASE IsNumeric(mycol) WHEN 1 THEN CAST(mycol AS FLOAT) ELSE 0 END
FROM    mytable

Answer 2

Try this:

SELECT CASE WHEN IsNumeric(YourColumn) = 0 THEN 
           0 
       ELSE 
           CAST(YourColumn AS decimal(18, 2)) 
       END

You have to adjust the destination data type, I have chosen decimal(18, 2) for demonstration.