When is a number not a number?

 Bob Dorr has posted about conversion issues with isnumeric

http://blogs.msdn.com/psssql/archive/2008/08/25/isnumeric-returns-1-true-and-i-expected-0-false.aspx?CommentPosted=true#commentmessage

This reminds me of a difficult "bug" in an application whereby a lookup actually entered a numeric value into a table from a text list. For the user the list was too long - why some developers think a drop down list of over a thousand entries is a "good idea" I never know - anyway the users had discovered that you could bypass the drop down to get an unknown by entering a full stop ( decimal point )  Now if you type

Select isnumeric('.');  this will return true/1 ,  however  select convert(int,'.');  will fail.

Sadly for the application in question the data entry didn't fail it was a process which occurred somewhat later - it took some considerable time to sort it out. Personally I wouldn't allow users to enter commas and full stops but it's worth noting that the logic behind a function doesn't always follow what we might consider "commonsense decisions" as amany a bug will prove.

 
Published 26 August 2008 11:57 by GrumpyOldDBA
Filed under:

Comments

# re: When is a number not a number?

26 August 2008 13:29 by Madhivanan

ISNUMERIC() is not reliable if you want to search for a numbers in a varchar column because it treats the numbers stored in sceintific format as numbers

Ex

Select

isnumeric('12d2'),

isnumeric('1000e12'),

isnumeric('$'),

isnumeric('-')

Also refer

sqlblogcasts.com/.../enhanced-isnumeric-function.aspx

# re: When is a number not a number?

26 August 2008 16:52 by GrumpyOldDBA

absolutely but until we can overload the standard system functions there's not much that can be done. Dropping custom funtions into the clr is an answer but does not resolve third party apps which use standard functions.

# re: When is a number not a number?

26 August 2008 19:40 by simonsabin

Isn't overload a developer phrase Colin?

I would have thought you would have to wash your mouth out after saying that :)

# re: When is a number not a number?

26 August 2008 21:00 by GrumpyOldDBA

sadly I have to admit to having trained as a programmer ( some years ago ) and to have actually been part of several development teams - but that was before I saw the light!