Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 ISNUMERIC Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, September 25, 2010 12:14 PM
 Ten Centuries Group: General Forum Members Last Login: 2 days ago @ 6:35 AM Points: 1,251, Visits: 1,596
Post #993286
 Posted Saturday, September 25, 2010 7:32 PM
 SSCrazy Group: General Forum Members Last Login: Thursday, March 27, 2014 8:50 AM Points: 2,163, Visits: 2,184
 Thanks for the question.This should be good to help people understand how ISNUMERIC works...
Post #993323
 Posted Sunday, September 26, 2010 10:18 PM
 SSChasing Mays Group: General Forum Members Last Login: Sunday, November 17, 2013 11:53 AM Points: 623, Visits: 237
 Good Question I got lost in Union all.
Post #993422
 Posted Sunday, September 26, 2010 10:48 PM
 Ten Centuries Group: General Forum Members Last Login: Sunday, November 25, 2012 6:31 PM Points: 1,375, Visits: 173
Post #993425
 Posted Sunday, September 26, 2010 11:54 PM
 UDP Broadcaster Group: General Forum Members Last Login: Thursday, November 21, 2013 11:33 PM Points: 1,481, Visits: 1,959
 UMG Developer (9/25/2010)Thanks for the question.This should be good to help people understand how ISNUMERIC works...Yeah... like total crap. Its borderline useless. Just to further demonstrate that. `select isnumeric('2e1')`This gives 1! An 'e' isnt in my book numeric. Now i know SQL thinks its a calculator and thinks '2e1' = 20 (it does the same with a 'd' instead of the 'e'). But seriously, thats stupid. Even more stupid is that even though SQL thinks this is 20 you cant convert it to an Int. You can however convert it to a Float... then to an Int... sigh. .NET doesnt do this. Old VB6 does... but atleast VB accepts it as a Int so its consistant. SQL is divided and isnt sure abouts its decision if it really is numeric or not. /T
Post #993440
 Posted Monday, September 27, 2010 12:28 AM
 SSChampion Group: General Forum Members Last Login: Today @ 1:07 AM Points: 12,245, Visits: 9,216
 Good question, although I seem to remember that I've seen similar ones.But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at LessThanDot. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #993451
 Posted Monday, September 27, 2010 12:47 AM
 UDP Broadcaster Group: General Forum Members Last Login: Thursday, November 21, 2013 11:33 PM Points: 1,481, Visits: 1,959
 da-zero (9/27/2010)Good question, although I seem to remember that I've seen similar ones.But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think. /T
Post #993460
 Posted Monday, September 27, 2010 12:57 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 6:26 AM Points: 1,529, Visits: 5,190
 da-zero (9/27/2010)Good question, although I seem to remember that I've seen similar ones.I'm sure there's been a very similar one in the past two or three months--I forget exactly when it was, though.
Post #993464
 Posted Monday, September 27, 2010 1:29 AM
 SSChampion Group: General Forum Members Last Login: Today @ 1:07 AM Points: 12,245, Visits: 9,216
 tommyh (9/27/2010)da-zero (9/27/2010)Good question, although I seem to remember that I've seen similar ones.But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think. /THow do you know 1+1 isn't 11 instead of 2? Maybe someone just typed in a + by accident. Let's just abandon all arithmetic operators, because SQL Server tries to think... How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at LessThanDot. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #993473
 Posted Monday, September 27, 2010 2:02 AM
 UDP Broadcaster Group: General Forum Members Last Login: Thursday, November 21, 2013 11:33 PM Points: 1,481, Visits: 1,959
 da-zero (9/27/2010)tommyh (9/27/2010)da-zero (9/27/2010)Good question, although I seem to remember that I've seen similar ones.But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think. /THow do you know 1+1 isn't 11 instead of 2? Maybe someone just typed in a + by accident. Let's just abandon all arithmetic operators, because SQL Server tries to think... True but atleast 1 and 1 are both numeric both in and out off context. Wheras "e" is only valid under some circumstances. With "11" you can do any SubString and still get a valid number. Try doing that on "2e1". Substring('2e1', 1, 2)... valid number... no. /T
Post #993491

 Permissions