Having conversion issues - String to Float

I am running into some issues with conversion and would appreciate someone who can help on this.

I need to roll up a column which contains numeric (float) data but it is stored in a varchar field.

I was trying to do the following (please note that Activity field is varchar(50) in MyTable):

SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable

and i get the following error.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value '-39862.8' to data type int.

I even tried with case statement (as below) but still got same issue.

SELECT CASE ISNUMERIC(NULLIF(Activity,0))

WHEN 1 THEN

CONVERT(float, (NULLIF(Activity,0)))

ELSE 0

END

from MyTable

Thanks,

Ashish

Try using 0.0 rather than 0 in the NULLIF functions. Also, do you mean to use ISNULL, or NULLIF? NULLIF returns a NULL, if the expressions are equal, while ISNULL returns the second argument if the first is NULL.
|||Yes. I want to use ISNULL. I changed it but I still get the error. Even with '0.0'|||What happens when you run the following:

Code Snippet

select *

from MyTable

where isnumeric(activity) = 0

|||SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable where isnumeric(Activity)=1|||

Are you sure that you want to use NULLIF()? (I would think that ISNULL() would be a better option.)

Also note that the isnumeric() test will PASS because '-39862.8' will always test to be a number.

This works as you want.

Code Snippet

DECLARE @.Activity varchar(50)

SET @.Activity = '-39862.8'

SELECT cast( isnull( @.Activity, 0 ) AS float )

--
-39862.800000000003

|||Good point about isnull(), Arnie.