isnumeric() yielding SURPRISING results - very odd

Question

Post reply

isnumeric() yielding SURPRISING results - very odd

jayaksql

SSC Enthusiast

Points: 126
More actions
July 19, 2005 at 3:14 pm

#102649

Hi all,
Using Microsoft SQL Server, I get this surprising result:
select isnumeric('0D830')
1
(1 row(s) affected)
I can't figure this out. I don't know if it is locale-related (I get the same results with N'0D830'), or what. I haven't found any other cases where it fails, but when I try several other characters instead of the D, it returns a 0. (actually an 'e' also gave the same result)
Does anyone have any ideas?
There must be a good explanation. I just don't know what it is.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Hex numbers are made up of digits '0123456789ABCDEF' and as you can see D is in that set

0D830 (hex) = 55344 (decimal)

Cheers!

* Noel

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Hmmm.

It's not a hex thing

select isnumeric('0A830') yields 0, so does

select isnumeric('0F830')

and

select isnumeric('1D830')

And this

select convert(int,'0D830')

just croaks as you would expect it to.

But this

select convert(float,'0D830')

returns 0

According to BOL, ISNUMERIC returns 1 if the value can be converted to a float, decimal, int or money data type. Looks like the server thinks it can do a float conversion on a value that starts with '0D' or '0E' and has at least one more digit. It appears to always evaluate to 0, no matter what you put after the '0D' or '0E'.

And then again, I might be wrong ...
David Webb

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

You're pretty close. It has nothing to do with hex.

D and E are used in Fortran to represent FLOATs in scientific notation. SQL Server internally uses C routines for ISNUMERIC that follow these formats. So ISNUMERIC returns 1 for 0d830 and 0e830, because they are evaluated to be convertible to FLOAT.

The reason why

select convert(float,'0D830')

returns 0 is simple. 0d830 is a shortcut to 0*10^830 which obviously is 0 for every INT you place after the d.

The reason why

select isnumeric('1D830')

return 0 is also simple. Try

select convert(float,'1D830')

and you'll get an overflow. 1*10^830 is simply to big for the FLOAT range

However, something like

select convert(float,'1d2'), convert(float,'1e2')

will work and return 100.0 (= 1*10^2)

ISNUMERIC has been buggy for years and MS is well aware of this behaviour. However, since they call it a "feature" it is unlikely to change.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jayaksql SSC Enthusiast Points: 126 More actions · Answer 4

Thank you very much! It seems like the MS documentation should mention this.

Anyway, this was my first post here and it is great to get helpful responses so quickly.

Carl B. SSCertifiable Points: 7931 More actions · Answer 5

I am aggree with you: BOL should mention it.

I would have search for a long time if Frank was not such an "encyclopedic" guy

Carl

Wayne West SSC-Insane Points: 22586 More actions · Answer 6

It's been ages since I worked in fortran, but Frank mentioning that brings that unfortunate aspect back.

I suppose the variable prefix made sense to some compiler designer, I'm just glad we're away from it now. 🙂

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

... actually not ... '0x0a' (whatever - I think char(10) ) is still valid in SQL server ... you might still be able to find it in some system sp's ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."