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.
>