Solomon Rutzky (9/4/2014)
Lynn Pettis (9/4/2014)
Solomon Rutzky (9/4/2014)
Second, this shouldn't be controversial since a NULL concatenated into a non-NULL string will (well, should) result in a NULL. This behavior can be changed via SET CONCAT_NULL_YIELDS_NULL OFF, but the MSDN documentation even says that in a future version it will always be ON. Basically, NULL does not have a string representation since an empty string is a valid string with a length of 0.Unless you happen to also work with Oracle. At this time Oracle treats an empty string as null. Messed me up for awhile until I read that in the documentation. It does say that this is subject to change in future versions of the product. This was Oracle 10 and 11.
Thanks Lynn. That is interesting. It has been so long since I have worked with Oracle that I either forgot that or I forgot that I never knew it to begin with ;-). I did find this info which sheds a little light on that seemingly odd behavior.
Eirikur Eiriksson (9/4/2014)
Solomon Rutzky (9/4/2014)
Second, this shouldn't be controversial since a NULL concatenated into a non-NULL string will (well, should) result in a NULL. This behavior can be changed via SET CONCAT_NULL_YIELDS_NULL OFF, but the MSDN documentation even says that in a future version it will always be ON. Basically, NULL does not have a string representation since an empty string is a valid string with a length of 0.Lets step back here, the function is used for presenting a delimited string data in a SQL Server tabular format, and the whole of the SQL Server unknown/missing logic (although somewhat thin as it does not differ between unknown, missing, no respond, empty, not applicable etc.) depends on NULLs. Now just think how much is going to fail down the line if suddenly NULLs become empty strings. It is no longer an unknown value but an empty value. This has nothing to do with the general NULL agreement/disagreement, it has everything to do with the logic we normally use. Bottom line is that if the value is not there it is truly unknown and in T-SQL or any SQL for that matter that is a NULL, only NULL and nothing (unintended pun here) but NULL.
Eirikur, I completely agree with the statements of "just think how much is going to fail down the line if suddenly NULLs become empty strings" and "if the value is not there it is truly unknown and ... that is a NULL, only NULL and nothing ... but NULL". This is why I agree with Jeff in disagreeing with the idea that the split function could, under some other interpretation, return NULL for any element. A NULL, as you stated, means "not there", hence could never have been part of the set that was joined together into a single, delimited string in the first place. If a set contains:
Bob
NULL
Sally
then the result of that set joined (as in String.Join(",")) together would be:
Bob,Sally
On the other hand, the following set:
Bob
{empty string}
Sally
would result in:
Bob,,Sally
Splitting either of those results on the comma would give you back the original set, with the exception, of course, of the missing element in the first set: the known unknown. If the reduced number of elements (due to NULLs not being represented in the CSV list) presents a problem for the operation, then either:
send separately the original number of elements (the diff between that and the result from the Split() would be the number of NULL elements) don't allow the source string fields to be NULL do an ISNULL / COALESCE such that NULLs are converted to an empty string or some other reserved designater that the down-stream code knows how to interpret Basically, in terms of SQL Server (and not Oracle ;-)), splitting a string can only return a NULL if the input itself is NULL.
Take care,
Solomon..
According to your logic what would a splitter return if the input string had a trailing comma?
Bob,Sally,
By no means am I saying I disagree with your sentiments just wondering. Is this a 2 element list or 3? Is the NULL there or is it not? Is the third element an empty string, a NULL or not returned?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 646 through 660 (of 990 total)
You must be logged in to reply to this topic. Login to reply