Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Britt Cluff » IsNumeric with NULL and Empty String
Add to briefcase
38 posts, Page 2 of 4
««1234»»»

IsNumeric with NULL and Empty String Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Stewart "Arturius" Campbell
Posted Thursday, August 04, 2011 3:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 3,164, Visits: 4,344
Nice, simple, back-to-basics question, thanks.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1154081
Britt Cluff
Posted Thursday, August 04, 2011 5:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
Looks like mostly good feedback. Glad most people have had a positive experience today.

http://brittcluff.blogspot.com/
Post #1154125
Thomas Abraham
Posted Thursday, August 04, 2011 5:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:11 AM
Points: 1,168, Visits: 1,470
Britt Cluff (8/4/2011)
Looks like mostly good feedback. Glad most people have had a positive experience today.


Wait for it. There will be someone that will hold a baby coyote in their left hand, a copy of a NYC subway map in their right, chant the names of the last 5 UN Secretary Generals, and manage to get a different result.

None of which will change my appreciation for a great question. Thanks. Count me as a "got it right for wrong reason" today. Good to be reminded of simple things like implicit conversion. I usually catch it in my own code, but maintain lots of code written by unseen developers.

Please don't go. The drones need you. They look up to you.
Post #1154152
L' Eomot Inversé
Posted Thursday, August 04, 2011 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 7,185, Visits: 7,285
Nice fun question.

I imagine some people will get the right answer for the wrong reason, but at least they will not b emoaning about "I want my point". More people will get it right for the right reason. So perhaps the proportion of wrong answers will be rather smaller than usual.

One potential gripe though: the reference given in the explanation doesn't explain what IsNumeric returns for NULL - but no-one should complain about that, as it probably isn't documented in BoL.

I've always found it a bit disappointing that IsNumeric(Null) doesn't return NULL; after all, a NULL value doesn't contain any non-numeric characters, so it's not not numeric; and it doesn't contan any characters that are numeric, so it's not numeric either; so it should surely return NULL; just yet another case where (T-)SQL gets it wrong with NULL. But then IsNumeric is such an awful function anyway that that's teh least of its worries (perhaps the biggest is its name).

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware
Post #1154171
Daniel Bowlin
Posted Thursday, August 04, 2011 7:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 2,681, Visits: 2,423
Implicit conversion is one of those issues, where if you know it, you just know. If you don't know it, the result is very unexpected. I am learning more and more of these, but I wonder, is there a singular document reference that lists all the implicit conversions that happen in SQL Server?
Post #1154200
Rafael Krisller
Posted Thursday, August 04, 2011 7:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 19, 2013 3:12 PM
Points: 369, Visits: 165

Nice. But try it.

DECLARE @NumericKey INT

SET @NumericKey = NULL

SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey

SET @NumericKey = ''

SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey
GO

DECLARE @NumericKey VARCHAR(30)

SET @NumericKey = NULL

SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey

SET @NumericKey = ''

SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey
Post #1154202
Toreador
Posted Thursday, August 04, 2011 7:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 1,384, Visits: 4,881
Tom.Thomson (8/4/2011)

I've always found it a bit disappointing that IsNumeric(Null) doesn't return NULL; after all, a NULL value doesn't contain any non-numeric characters, so it's not not numeric; and it doesn't contain any characters that are numeric, so it's not numeric either; so it should surely return NULL; just yet another case where (T-)SQL gets it wrong with NULL. But then IsNumeric is such an awful function anyway that that's teh least of its worries (perhaps the biggest is its name).


Thanks for that - I've learned something new today!

I always thought that the justification for things like '+' or '$' returning 1 for isNumeric was that it was possible to assign them to at least one numeric datatype, in this case money. But on that basis, shouldn't IsNumeric(NULL) also return 1, since NULL can be assigned to any numeric datatype? I'd have guessed at it returning NULL, but to return 0 is just bizarre.
Post #1154232
Surii
Posted Thursday, August 04, 2011 8:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, June 13, 2013 10:57 AM
Points: 659, Visits: 465
Good question!!
Post #1154256
Cliff Jones
Posted Thursday, August 04, 2011 8:15 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 3,423, Visits: 3,439
Thomas Abraham (8/4/2011)
Britt Cluff (8/4/2011)
Looks like mostly good feedback. Glad most people have had a positive experience today.


Wait for it. There will be someone that will hold a baby coyote in their left hand, a copy of a NYC subway map in their right, chant the names of the last 5 UN Secretary Generals, and manage to get a different result.

None of which will change my appreciation for a great question. Thanks. Count me as a "got it right for wrong reason" today. Good to be reminded of simple things like implicit conversion. I usually catch it in my own code, but maintain lots of code written by unseen developers.


Great comment. I'm just glad there wasn't an 'It Depends' as a possible answer.
Post #1154258
Paul White
Posted Thursday, August 04, 2011 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578
IsNumeric - sigh.

Thank goodness for the improvements in the next version of SQL Server like TRY_CONVERT.


Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1154287
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.