If the number is expressed in scientific format (eg 12d2, 12e2), ISNUMERIC treat that as number. However when you append e0 the number would be considered as nonnumeric, if it is already in a specific format. The same case applies when you use d0 in place of e0

--It's interesting to note which of the values can actually be converted to a numeric type:

--Try adding convert(numeric,data) to the select:

SELECT Data,

ISNUMERIC(Data) AS [IsNumeric],

ISNUMERIC(Data + 'e0') AS IsReallyNumeric,

Data + 'e0',

convert(numeric,data)

FROM #Temp

--If #temp contains any values that are not "really numeric", you get:

Msg 8114, Level 16, State 5, Line 12

Error converting data type varchar to numeric.

go figure...

So what is Isnumeric for again?

Next, try this:

SELECT Data,

ISNUMERIC(Data) AS [IsNumeric],

ISNUMERIC(Data + 'e0') AS IsReallyNumeric,

Data + 'e0',

convert(float,data)

FROM #Temp

Now which ones get "Error converting data type varchar to numeric."

how about convert(money,data)?

and cast (ascii(data) as int)

So for next time Denis, how do I write an expression that returns some kind of numeric type and does not produce an error message for all of the original data in #temp?

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/07/02/Enhanced-ISNUMERIC---function.aspx

Denis Gobo: "in my opinion money should never be used and removed from SQL server because of rounding issues"

Can you explain that one, perhaps with an example?

I've never come across any such issues!

George: "Can you explain that one, perhaps with an example?"

Just copy and paste this code into SSMS and hit execute:

DECLARE @M1 money, @M2 money

DECLARE @N1 numeric(12,4), @N2 numeric(12,4);

DECLARE @F1 float, @F2 float;

SET @M1 = 12.3456;

SET @M2 = 78.9012;

SET @N1 = @M1;

SET @N2 = @M2;

SET @F1 = @M1;

SET @F2 = @M2;

SELECT (@M1 / @M2) * @M2;

SELECT (@N1 / @N2) * @N2;

SELECT (@F1 / @F2) * @F2;

Best, Hugo