Bug in ISNUMERIC ?

strange thing I just ran into, not sure if this is a bug or what ... but pretty annoying.
In MS SQLServer 2000 :

SELECT (ISNUMERIC('0E010101'))

returns "1"

but

SELECT CAST ('0E010101' AS numeric)

returns "Error converting data type varchar to numeric"

any idea?ISNUMERIC tests whether a value can be converted to a number of any type.

CAST ('0E010101' AS numeric) attempts to specifically convert the string to the SQL NUMERIC data type.|||I think he was addressing the fact that there was an 'E' in the numeric string.

Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

I dont know but it sounds good to me.|||No, same happens with D.
I thought it could be Hexadecimal, but it doesn't seem to work with ABCFGH
?

Originally posted by Ruprect
Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

Could Be|||Originally posted by Ruprect
I think he was addressing the fact that there was an 'E' in the numeric string.

Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

I dont know but it sounds good to me.

I tried the following statement and it gives a 0 so i am going with my Hypothesis
SELECT (ISNUMERIC('0A010101'))|||try SELECT (ISNUMERIC('0D010101'))

Originally posted by Ruprect
I tried the following statement and it gives a 0 so i am going with my Hypothesis
SELECT (ISNUMERIC('0A010101'))|||The strange thing is that SELECT (ISNUMERIC('0DE010101')) returns 0
but
SELECT (ISNUMERIC('0D010101'))
or
SELECT (ISNUMERIC('0E010101'))
return 1

Originally posted by Tontxu
try SELECT (ISNUMERIC('0D010101'))|||Books Online ISNUMERIC
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

i even tried different combiantions and added an e to the end it appears that it's reading the values with on char in the second position
no i need to create a script that trys a brute force on every combination

nahhh|||I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something...|||i retracted my d statement
i thought that it could have been indicative of some type of money
danish kroners or whatever they buy their crap with.|||the only thing I know is that it's not only the second position as my initial problem was with
SELECT (ISNUMERIC('0102D121'))

my problem was :

DECLARE @.Temp nvachar(25)

SELECT @.Temp = '0000D121'
SELECT
CASE
WHEN ISNUMERIC (@.Temp) THEN CONVERT(nvarchar(20),convert(numeric,@.Temp))
ELSE @.Temp
END

the problem is that @.Temp is gonna be a colum from a table so I can predict the values and I'm trying to remove the leading '0'

Originally posted by rokslide
I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something...|||there is a bug with converting to numeric.

try:

select convert(float,1.7256e+06)
select convert(float,'1.7256e+06')
select convert(numeric(38,19),1.7256e+06)
select convert(numeric(38,19),'1.7256e+06')

in query analyzer...

Bill|||Originally posted by rokslide
I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something... Great zot! No old C programmers around anymore, or what ?!?!

E is the exponent introducer for real (float*4, 7 digit precision) values. D is the introducer for double (float*8, 53 digit precision) values. M is a suffix for (packed) decimal, and even IsNumeric() fails to process M correctly.

-PatP|||A history lesson!

Do old C programmers ever die? Or does their legacy go on?|||Damn...learned something new again...|||I Win!!!!

and i guessed

we are the champions! We Are the champions!Playing in the background|||Now that you have won, Ruprect, you can keep your prize and walk away, or you can swap your prize for what is in thread number 3. ;-)|||Hold on! I demand a recount.|||winning is more than actually doing something.. it's making everyone believe that you did something and first no less.

and welll keep on fighting til the end... ba bummmm:o
we are the champions :o
we are the champions :o
:o
of the world......:o

sorry. it must be all the cough syrup i had at lunch today...

:p