## ISNUMERIC

 Author Message sknox SSCertifiable Group: General Forum Members Points: 5599 Visits: 3032 Comments posted to this topic are about the item ISNUMERIC UMG Developer SSCertifiable Group: General Forum Members Points: 5918 Visits: 2204 Thanks for the question.This should be good to help people understand how ISNUMERIC works... Dhruvesh Shah Ten Centuries Group: General Forum Members Points: 1049 Visits: 237 Good Question I got lost in Union all. subbu_e SSCommitted Group: General Forum Members Points: 1749 Visits: 173 yes this really good question Regards,SubbuClick here to Get Speedy answer or solution tommyh Hall of Fame Group: General Forum Members Points: 3434 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 SSC Guru Group: General Forum Members Points: 111026 Visits: 13338 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 Hall of Fame Group: General Forum Members Points: 3434 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 SSCertifiable Group: General Forum Members Points: 6362 Visits: 6240 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 SSC Guru Group: General Forum Members Points: 111026 Visits: 13338 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 Hall of Fame Group: General Forum Members Points: 3434 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