Tom.Thomson (4/12/2010)
Oh dear :unsure:. This makes me think maybe Paul was right when he said my post was unclear.
No, you're right. As I said, I didn't read the whole thread carefully, and may have projected my own reading of what is counterintuitive onto your comments about the LEFT function. I re-read your initial comment and it is indeed about the LEFT function behavior.
I still think it would help if the ISNULL documentation had a varchar example, though.
webrunner (4/12/2010)
IAfter all, I agree with Tom that it is counterintuitive to provide a function that ostensibly allows you to provide replacement text of your choice, when in reality that text may be truncated to the exact type AND length of the type specificed.Actually I don't think it's counterintuitive - the type of the result is the same as the type of the check expression, and in the case of varchar, varbinary, and nvarchar the type includes specification of the maximum permitted length. It's the behaviour of LEFT that surprised me, because I expected it to deliver a result with the same type as its first argument, just as IsNull does.
Interesting - by "type" do you mean type and length, such as varchar(10)? In that case, I never thought about it, but it does make sense. A LEFT function performs a kind of substring operation, so I can see that it would return a value matching the length chosen in the second parameter. If by "type" you mean type only (varchar vs. varbinary, etc.), though, I just thought the ISNULL function would override that and ensure that the result of ISNULL would be long enough to accommodate the entire replacement value chosen. That was ignorance on my part - in reality it seems one just has to be careful to make sure the lengths work out; I guess it may also be ignorance on my part not to be surprised by the behavior of LEFT(). 🙂
2. Going back to the ostensible logic of the ISNULL behavior, I can certainly understand why Tom would be, if not flabbergasted, then baffled (as Paul put it) by this behavior, prior to learning more about it.
The IsNull behaviour is nice and clean and tidy, Paul and I have no disagreement on that. In fact what we mostly disagreed about was words (words badly chosen by each of us, I think) not anything in T-SQL.
Yes, as I said above, if "type" means "type and length," then the ISNULL documentation make sense to me now. As far as LEFT is concerned, I can see the logic of returning the length that was chosen for the second parameter, but I suppose I can see the logic of keeping the length that was passed in. I guess the existing behavior didn't surprise me as much as it did you.
I don't totally agree with you guys regarding ISNULL with varchars - especially since it seems that the existence of the COALESCE function** means that someone thought returning the whole string would be useful in certain cases - but I think as long as the behavior of ISNULL with a varchar is explained properly I can live with it.
** For example:
DECLARE @t VARCHAR(4)
SELECT ISNULL(LEFT(@t,2), 'Open'), COALESCE(LEFT(@t,2), 'Open')
On a different topic: my first QoTD is due to appear in 8 to 10 days time; I've tried to choose something that I can't get wrong, so that I don't suffer the bashing that many QoTD setters get, but I'm not at all sure I've succeded.
Good for you. I've never tried a question and, frankly, still consider myself unqualified to post one that wouldn't get torched for inaccuracy.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html