SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ISNULL


ISNULL

Author
Message
chrisfradenburg
chrisfradenburg
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3038 Visits: 2077
OzYbOi d(-_-)b (11/23/2011)
Certainly a little curious as to why only the Decimal behaves in this manor... But this is something that I have run across before in a shop where implicit conversions of data was the normal practice. Crazy


It's not just decimal. Numeric will fail as well. And it's also not just implicit conversions. Explicit will fail as well.

select CONVERT(decimal(18,6),'')
go
select CONVERT(numeric(18,6),'')
go


OzYbOi d(-_-)b
OzYbOi d(-_-)b
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1718 Visits: 778
cfradenburg (11/23/2011)
OzYbOi d(-_-)b (11/23/2011)
Certainly a little curious as to why only the Decimal behaves in this manor... But this is something that I have run across before in a shop where implicit conversions of data was the normal practice. Crazy


It's not just decimal. Numeric will fail as well. And it's also not just implicit conversions. Explicit will fail as well.

select CONVERT(decimal(18,6),'')
go
select CONVERT(numeric(18,6),'')
go



Sorry for the abiguity of my earlier statement and although the response was in the narrower context of the question, yes, I could have noted NUMERIC also. ;-)

my curiosity stems from the apparent inconsistency where SQL Server allows for other datatypes such as INTEGER, FLOAT & MONEY to implicitly convert empty string to their equivalent zero value, but not for DECIMAL and NUMERIC. Cool
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12717 Visits: 5010
JestersGrind (11/23/2011)
Archie,

You are correct. That was an over sight on my part. I didn't test it with SQL 2000. The question should have stated that this was for SQL 2005 and higher. My apologies.

Thanks,

Greg


I think that with SQLS2012 RTM and available for download, we should reverse the logic and note specifically when the question also applies to 2000.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19141 Visits: 12426
archie flockhart (11/23/2011)
In the BOL, I still don't see anything that goes beyond "for some reason" as an explanation of why you can't convert an empty string to decimal, while you can convert it to integer or float or bit.


L' Eomot Inversé (11/23/2011)
That explicit documentation doesn't give any explanation either.


Apparently, I didn't make myself sufficiently clear.

It's true that Books Online doesn't explain WHY SQL Server behaves this way. But that is true of a lot, probably almost all, articles in Books Online. Why is an integer stored in 4 bytes? Why is INTO optional in the INSERT statement? Why is EXEC[UTE] optional at the start of a batch but mandatory at other places? Some of these will have very good reasons, others maybe not. But the point is that they are all documented.

In a question of the day, I think the explanation should make clear why the given answer is correct. Not "why" as in "why did the SQL Server team make it this way", but "why" as in "what documented feature (*) causes this effect"? That's what I was missing in the explanation. The link to the CAST and CONVERT article would have provided that explanation, as this article clearly documents that this is intended behaviour.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3682 Visits: 3059
A good QOD today for a couple reasons. The first, of course, is that it brings to our attention the rather cryptic behavior of SQL not being able to convert an empty string to DECIMAL even though it will happily do so to other numeric types (yeah, "numeric types", but not "type numeric". Golly, I love this sort of crazy terminology....).

The second reason hasn't been mentioned (that I see). I was looking for an option of "All the selects will fail". Why? The code looks as though the author wants to return either an non-null numeric or an empty string, but instead gets a zero. I'm not arguing against SQL's data-typing enforcement as demonstrated here. Rather, I'd emphasize that "proper" coding would obviate the implicit conversion altogether by using a zero as the second operand.

declare @money money
,@bit bit
,@decimal decimal
select isnull(@decimal, 0.0)
select isnull(@money, 0.0)
select isnull(@bit, 0)



And if the empty string were the real objective, an explicit conversion would be needed.

declare  @money money
,@bit bit
,@decimal decimal
select isnull(convert(char(20),@decimal), '')
select isnull(convert(char(20),@money), '')
select isnull(convert(char(20),@bit), '')


Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2307 Visits: 1515
michael.kaufmann (11/23/2011)
In case anyone gets stuck at the conversion table (which indicates implicit conversion between character and numeric data types), keep on reading to find this little comment towards the end:
SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal..


Thank you. That's exactly what I was looking for.
Maybe the BOL team should add that kind of comments near the Implicit Conversion chart to minimize confusion?

Anyway a great and intriguing QOTD. Thanks.

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64578 Visits: 13298
Nice question, thanks.


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
DBA_Dom
DBA_Dom
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1044 Visits: 81110
I liked this question. It helps add a little perspective when deciding what data type should be used if there is a toss up between float , money or decimal. And if decimal is chosen as the data type then what restrictions there are in handling. Learned a little something new today.
Britt Cluff
Britt Cluff
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2395 Visits: 253
Almost picked none would fail, then did a quick investigation. Learned something new today. Thanks for the question.

http://brittcluff.blogspot.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search