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 ««1234»»»

IsNumeric with NULL and Empty String Expand / Collapse
Author
Message
Posted Thursday, August 4, 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: Yesterday @ 9:32 PM
Points: 3,982, Visits: 5,229
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
Posted Thursday, August 4, 2011 5:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Looks like mostly good feedback. Glad most people have had a positive experience today.

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 1,837, Visits: 2,180
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.
Connect to me on LinkedIn
Post #1154152
Posted Thursday, August 4, 2011 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 7,739, Visits: 9,488
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
Post #1154171
Posted Thursday, August 4, 2011 7:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
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
Posted Thursday, August 4, 2011 7:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:09 AM
Points: 395, Visits: 168

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
Posted Thursday, August 4, 2011 7:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 1,764, Visits: 6,379
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
Posted Thursday, August 4, 2011 8:11 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:26 AM
Points: 668, Visits: 485
Good question!!
Post #1154256
Posted Thursday, August 4, 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: Thursday, September 25, 2014 8:01 AM
Points: 3,941, Visits: 3,639
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
Posted Thursday, August 4, 2011 8:41 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
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 ««1234»»»

Permissions Expand / Collapse