BUG REPORT: SQL Server ISNUMERIC() not reliable in all cases

I have no idea how to submit this bug report to Microsoft, and I'm sick of
searching the site and ending up in the same place all the time. So hopefull
y
somebody can fwd this to them or one of their developers may stumble across
it.
Quote from Documentation:
ISNUMERIC
Determines whether an expression is a valid numeric type.
Syntax
ISNUMERIC ( expression )
Arguments
expression
Is an expression to be evaluated.
Return Types
int
Remarks
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 thes
e
numeric types.
The last statement is not correct. A return of '1' does not guarantee this.
Try:
ISNUMERIC('5') and you get 1, which is correct.
ISNUMERIC('5.0') and you get 1, which is correct.
ISNUMERIC('5,0') and you get 1, which is correct.
[may depend on your config. settings, using commas as decimal markers]
But the following also return 1, which is NOT correct:
ISNUMERIC('.')
ISNUMERIC(',')
as these cannot be converted to integers.
Thanks
Richard McSharry> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
Nobody said these could be converted to integers. "A return value of 1
guarantees that expression can be converted to *ONE* of these numeric
types" (my emphasis). Those strings WILL convert to MONEY:
SELECT CAST('.' AS MONEY)
SELECT CAST(',' AS MONEY)
ISNUMERIC is pretty useless most of the time but this is not a bug.

> I have no idea how to submit this bug report to Microsoft
Contact Product Support: http://support.microsoft.com/
However, you may want to post here first (Not everything that you think
is a bug will be :-) )
David Portas
SQL Server MVP
--|||select cast('.' as money)
Works for me.
From BOL:
A return value of 1 guarantees that the expression can be converted to ONE
of these numeric types.
Well, there is error in this statement. It should say ...to ONE OR MORE of
these numeric types.
"Hyper" <Hyper@.discussions.microsoft.com> wrote in message
news:348D7AD8-0F29-44AC-B5BF-E07E09CF415C@.microsoft.com...
>I have no idea how to submit this bug report to Microsoft, and I'm sick of
> searching the site and ending up in the same place all the time. So
> hopefully
> somebody can fwd this to them or one of their developers may stumble
> across
> it.
> Quote from Documentation:
> ISNUMERIC
> Determines whether an expression is a valid numeric type.
> Syntax
> ISNUMERIC ( expression )
> Arguments
> expression
> Is an expression to be evaluated.
> Return Types
> int
> Remarks
> 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.
>
> The last statement is not correct. A return of '1' does not guarantee
> this.
> Try:
> ISNUMERIC('5') and you get 1, which is correct.
> ISNUMERIC('5.0') and you get 1, which is correct.
> ISNUMERIC('5,0') and you get 1, which is correct.
> [may depend on your config. settings, using commas as decimal markers]
> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
> Thanks
> Richard McSharry|||"Hyper" <Hyper@.discussions.microsoft.com> wrote in message
news:348D7AD8-0F29-44AC-B5BF-E07E09CF415C@.microsoft.com...
>I have no idea how to submit this bug report to Microsoft, and I'm sick of
> searching the site and ending up in the same place all the time. So
> hopefully
> somebody can fwd this to them or one of their developers may stumble
> across
> it.
> Quote from Documentation:
> ISNUMERIC
> Determines whether an expression is a valid numeric type.
> Syntax
> ISNUMERIC ( expression )
> Arguments
> expression
> Is an expression to be evaluated.
> Return Types
> int
> Remarks
> 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.
>
> The last statement is not correct. A return of '1' does not guarantee
> this.
> Try:
> ISNUMERIC('5') and you get 1, which is correct.
> ISNUMERIC('5.0') and you get 1, which is correct.
> ISNUMERIC('5,0') and you get 1, which is correct.
> [may depend on your config. settings, using commas as decimal markers]
> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
> Thanks
> Richard McSharry
They can be converted to MONEY.|||Perhaps I'm missing something, but why is '.' a valid Money type value? I se
e
that it casts to .0000, but why? What is the logic behind allowing a period
(or
comma) by itself as a value?
Thomas
"Hyper" <Hyper@.discussions.microsoft.com> wrote in message
news:348D7AD8-0F29-44AC-B5BF-E07E09CF415C@.microsoft.com...
>I have no idea how to submit this bug report to Microsoft, and I'm sick of
> searching the site and ending up in the same place all the time. So hopefu
lly
> somebody can fwd this to them or one of their developers may stumble acros
s
> it.
> Quote from Documentation:
> ISNUMERIC
> Determines whether an expression is a valid numeric type.
> Syntax
> ISNUMERIC ( expression )
> Arguments
> expression
> Is an expression to be evaluated.
> Return Types
> int
> Remarks
> 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 th
ese
> numeric types.
>
> The last statement is not correct. A return of '1' does not guarantee this
.
> Try:
> ISNUMERIC('5') and you get 1, which is correct.
> ISNUMERIC('5.0') and you get 1, which is correct.
> ISNUMERIC('5,0') and you get 1, which is correct.
> [may depend on your config. settings, using commas as decimal markers]
> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
> Thanks
> Richard McSharry|||>> Perhaps I'm missing something, but why is '.' a valid Money type
value? <<
It is a proprietary data type, so they can do anything they wish with
it. Yet another reason never to use a proprietary data type. I would
guess this is part of the old "Sybase Code Museum" that SQL Server
still has in it.|||Is the IsNumeric function part of the official ISO SQL specification? If so,
does that specification define the rules that determine a numeric value? Kee
p in
mind that in this case, even if you did not use a proprietary data type,
IsNumeric would still return what appears to be a bogus result.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1113862850.783170.196580@.l41g2000cwc.googlegroups.com...
> value? <<
> It is a proprietary data type, so they can do anything they wish with
> it. Yet another reason never to use a proprietary data type. I would
> guess this is part of the old "Sybase Code Museum" that SQL Server
> still has in it.
>

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

BUG - ISNUMERIC('.') = 1

ISNUMERIC('.') returns a value of 1.
This is bad, right?
Howardhttp://www.aspfaq.com/2390
"Howard Swope" <swopehATgigamotoDOTcom> wrote in message
news:eockploLGHA.3100@.tk2msftngp13.phx.gbl...
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
> Howard
>|||Howard Swope wrote:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
>
Try this to see why isnumeric returns 1 for this string:
select cast('.' as money)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Howard Swope (swopehATgigamotoDOTcom) writes:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
Yes isnumeric() is bad. It returns 1 if the value can be converted to
any numeric data type. Which is quite a useles piece of information.
It's not a bug though.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx