Bug in IsNumeric? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bug in IsNumeric?

Author

Topic

ocortess
Starting Member

4 Posts

Posted - 2004-10-04 : 13:21:58

This expression is returning 1, I was expecting a 0. Does anybody know if this is a bug?

select isnumeric('32D-3255')

is there any other way to check this?

Thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-04 : 14:06:19

That is a valid numeric (float) so it's not a bug.
What do you think a numeric is?
If you just want digits then
fld not like '%[^0-9]%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-04 : 14:10:07

Nigel --

What value does 32D-3255 evaluate to? What does the "D" mean? I admit, I've never seen that notation before that I can recall ....

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-04 : 14:14:10

me neither...
dr.: i guess we're too young

Go with the flow & have fun! Else fight the flow

ocortess
Starting Member

4 Posts

Posted - 2004-10-04 : 15:14:48

I see what you are saying. How would look for strings that contain only ceros?. ex. '000','0','000000000'.

Thanks,

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-04 : 15:15:57

where fld not like '%[^0]%'

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-04 : 15:16:04

it's not bug, it's feature :)
for SqlServe it's syntactically looks as real or float constant
One more interesting result
pls try
select 123D45

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-04 : 15:24:36

yeah but i'd still like to know what result 32D-3255 gives back when run...

quote:
select 123D45

this is funny. why is it like that?????

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-04 : 15:26:14

What does that evaulate to, though? What does the "D" mean? For example, we know 1E3 evaluates to 100 -- what about 123D45 ?

actually, your example is not good -- it has nothing to do with conversion to numeric values, it has to do with how SQL parses a SELECT statement. Try this:

select 123WhatDoesThisReturn

Who would've guessed that the above would be legal? but it certainly isn't a numeric expression taken as a whole.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-04 : 15:29:29

i got it:
select convert(float, '1d-3')

Go with the flow & have fun! Else fight the flow

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-04 : 16:09:21

"There are more things in heaven and earth, Horatio,Than are dreamt of in your philosophy."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-05 : 03:44:14

good old hamlet

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-05 : 03:45:32

quote:
this is funny. why is it like that?????

FORTRAN.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-05 : 03:57:12

fortran??? emm.... what does fortran have to do with it???
because select 123d45 returns
d45 - column name
123 - column value

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-05 : 07:48:26

quote:
Originally posted by spirit1
select 123d45 returns
d45 - column name
123 - column value

Yeah, but that's just the lexer tokenizing at the d. That would happen with any letter except e.

SELECT CAST('123D45' AS float)
returns
1.2299999999999999E+47
When a string is converted to a floating point number in SQL Server 'D' is treated the same as 'E': as an exponent. The use of 'D' as an mantissa-exponent separator originates (as I understand it) in FORTRAN, where it was used to indicate that the number was double precision. Or something.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-05 : 08:00:23

aaaahhhh. great. thanx.

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-05 : 08:01:34

I knew Mr. Fribble would know something about this !

Thanks Arnold

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-05 : 08:31:07

[code]
SELECT n, c
FROM (
SELECT n, NCHAR(n) AS c
FROM Numbers
WHERE n BETWEEN 0 AND 65535
) AS A
WHERE ISNUMERIC(N'1' + c + N'1') = 1
AND ISNUMERIC(c) = 0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources