Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

ISNUMERIC Expand / Collapse
Author
Message
Posted Saturday, September 25, 2010 12:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 1,295, Visits: 1,652
Comments posted to this topic are about the item ISNUMERIC
Post #993286
Posted Saturday, September 25, 2010 7:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, November 25, 2012 6:31 PM
Points: 1,375, Visits: 173
yes this really good question

Regards,
Subbu
Click here to Get Speedy answer or solution
Post #993425
Posted Sunday, September 26, 2010 11:54 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
UMG Developer (9/25/2010)
Thanks for the question.

This should be good to help people understand how ISNUMERIC works...


<rant>
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.
</rant off>

/T
Post #993440
Posted Monday, September 27, 2010 12:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
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

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:12 AM
Points: 1,610, Visits: 5,482
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
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.

/T


How 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

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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.

/T


How 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
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse