## ISNUMERIC

 Author Message sknox SSCrazy Group: General Forum Members Points: 2135 Visits: 2747 Comments posted to this topic are about the item ISNUMERIC UMG Developer SSCrazy Group: General Forum Members Points: 2368 Visits: 2204 Thanks for the question.This should be good to help people understand how ISNUMERIC works... Dhruvesh Shah SSChasing Mays Group: General Forum Members Points: 637 Visits: 237 Good Question I got lost in Union all. subbu_e Ten Centuries Group: General Forum Members Points: 1381 Visits: 173 yes this really good question Regards,SubbuClick here to Get Speedy answer or solution tommyh SSCommitted Group: General Forum Members Points: 1600 Visits: 2000 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 Koen Verbeeck SSCoach Group: General Forum Members Points: 18485 Visits: 13247 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?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP tommyh SSCommitted Group: General Forum Members Points: 1600 Visits: 2000 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 paul.knibbs SSCrazy Group: General Forum Members Points: 2102 Visits: 6222 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. Koen Verbeeck SSCoach Group: General Forum Members Points: 18485 Visits: 13247 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?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP tommyh SSCommitted Group: General Forum Members Points: 1600 Visits: 2000 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