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
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 6:02 AM Points: 1,858, Visits: 2,652
Post #993286
 Posted Saturday, September 25, 2010 7:32 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 30, 2015 9:47 AM Points: 2,160, Visits: 2,204
 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
 SSCommitted Group: General Forum Members Last Login: Tuesday, February 17, 2015 6:07 AM Points: 1,526, Visits: 2,000
 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
 SSCoach Group: General Forum Members Last Login: Today @ 2:09 AM Points: 15,501, Visits: 13,163
 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 SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #993451
 Posted Monday, September 27, 2010 12:47 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, February 17, 2015 6:07 AM Points: 1,526, Visits: 2,000
 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: Friday, October 14, 2016 2:57 AM Points: 1,833, Visits: 6,210
 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
 SSCoach Group: General Forum Members Last Login: Today @ 2:09 AM Points: 15,501, Visits: 13,163
 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 SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #993473
 Posted Monday, September 27, 2010 2:02 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, February 17, 2015 6:07 AM Points: 1,526, Visits: 2,000
 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