Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CONCAT 1


CONCAT 1

Author
Message
Dave62
Dave62
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: 3049 Visits: 2713
Hugo Kornelis (8/23/2013)
...
I've had to work with counters that corresponded to C# zero-based arrays. So "3" represents four elements, and if you combine two of those arrays, you have "7" (representing eight) elements. For that specific use case, it would have been practical if 3 + 3 would evaluate to 7. If someone would redefine addition to work that way, it would save me the effort of having to explicitly write "+1" to my additions. Okay, it may fly in the face of all mathematical theory - but it is SO PRACTICAL (in that specific situation).

Does the request to redefine addition this way sound absurd to you? Well, the request to redefine concatenation of NULL values is exactly like that.
...

Actually, I've been a developer for decades longer than I've been a DBA so zero-based counting does not fly in the face of all mathematical theory nor sound absurd to me. If I add two Hex numbers 7 + 7 = E.

No the mathematical universe did not just implode. It's perfectly correct when one understands the reference is Hex-based.

This is why I'm in complete agreement with the implementation of the new function. Combining "no data present" to any known value(s) = the known value(s) seems perfectly correct.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11548
L' Eomot Inversé (8/23/2013)
Hugo Kornelis (8/23/2013)

Now you're doing the same as you did in the start - simplyfying "it isn't known to the database" to "it isn't known". You are far from alone in that confusion
....
....
And when I write "WHERE is_smoker IS NULL", I am indeed looking for people for whom we do not know whether they smoke or not.


And there you fall into exactly the simplification that I use. So if it is indeed a confusion, I have you for company.


No, there I apply my knowledge of the context. I know that in the context of the is_smoker column, a NULL represents unknown. I thought that was clear from the context of the message.


It's all very well to talk about the context; but often when you come to look at some SQL you don't have that context - you just have the code, including if you are lucky the scripts that generate the schema and all its constraints, and of course you have the data. (...)

(Shortened the quote down to the essence)

Or another way to put it: "In theory, there is no difference between theory and practice. In practice, there is".

And yet, when we work with a table and see the number 42 in a column, we can usually deduct from the limited context available (column name, existing code, other data in the same column) how to interpret it. The same applies for a NULL, In both cases, the deduction may not be correct all the time, but most of the time, we do get it right.


I'll leave it at this. It's late and I've worked way too hard today, so I need to relax and sleep. After I respond on one other forum topic.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
David Conn
David Conn
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: 3034 Visits: 1116
This is a good QOTD. Obviously brought out a lively discussion about Nulls.

Microsoft have done a good job of providing a Function that works the way most people would prefer to deal with Nulls i.e. by not having an everyday operation such as building someones Name out of its component Columns come to grief by Nulls in some of the Columns.

David
tilew-948340
tilew-948340
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 2437
Just to add my tiny experience here about Null, if I may.

I had those statistics Views created from many tables. Every source tables were loaded from different group of people, but the data were all related, so the group "A" has to create one reference so group "B" and "C" could enter theirs info.

As the group "A" may not has received all the information while creating the reference case, many columns would be "NULL", being entered later, but sometimes (for specific column) there could be some "really unknown" data (the data is impossible to get for any reason) and other time their could be no data to enter at all (the data is not applicable for the type of reference)

So, three cases of a completely different meaning for an empty column value. The solution for me (who had really not much SQL experience) was simple: write something of leave it "NULL" and create the Views with an "IsNull" for the specific colum (if is "Null", write 'not received yet' or write 'will be known next month', etc. depending of the specificity ot the View)

_ NULL value = data never was entered/received
_ Unk = Data was/will never be acquired (mostly because of human error)
_ N/A = Nothing as data is not applicable (nothing could be acquired for the type of that reference)

Surely, if the column would have been integer values, I would had to find an other solution, but my point here is that Null (for me) really means more than nothing, more than the emptyness of the space: it means the "nothingness of the emptyness" before its creation

So, it is logic to me that concatenating a Null to a value put "nothing" beside something ("Null" is not created so it does not exist), but adding (+) something to whatever is not created (Null) makes that something absorbed and becomes something to be created (like putting a planet in a black hole, but my comparaison is bad because a black hole is a compact mass of something, so...)

Hope I did not disturb anyone with my vision...
logitestus
logitestus
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 505
When I first saw this question, I hadn't used the CONCAT function so I read up on it. I should have gotten the question right based off of that information but I wanted to verify so I ran the query. COLLATION ERROR!! When I re-ran within the context of the tempdb, success. Unfortunately by then I had already failed the QotD. My only excuse: I sooo need to wait for my first cup of coffee to take effect before trying to think...

Great Question!
sqlnaive
sqlnaive
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: 3683 Visits: 2774
I have yet to work on SQL 2012. So it was very refreshing to learn about one new function through QOTD.

Only if life was that easy... :-):-):-)
jfgoude
jfgoude
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 299
same thing I do not work with SQL 2012 but was just reading about CONCAT on mssqlTips billet
so +1

and bravo to our two scribers it seems there are both right depending on the useability we need
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
I got an email suggesting somone had misinterpreted my comment here in a strange way, so I thought I should clafify.

L' Eomot Inversé (8/23/2013)
Hugo Kornelis

(Oh, one more thing - the example af the start of your post painfully reminded me of the fact that the current version of the ANSI standard for SQL actually defines a data type for truth values. They call it boolean (shudder!), and even explicitly specify that the truth value "unknown" and the situation where a truth value is missing should both be represented by the null mark (shudder even more!).


I agree with your first shudder. Boolean logic is not 3-valued, so calling this type boolean is just plain silly. Your "shudder even more" I can't agree with - what else should one do when the value of a so-called Boolean is not known in the database? Apart from the name, there's nothing wrong with this type.

What I meant was not that the only unmarked values in the domain (type) should be 'TRUE'and 'FALSE'; that would be a genuine boolean type. A type representing a three valued logic should have 3 unmarked values: 'TRUE', 'FALSE', and 'NEITHER' (and preferably not be called BOOLEAN). I may have misunderstood Hugo, and misread his statement of what's in the current ANSI standard (of which I don't have a copy, so I didn't check there), and the message I received indicated that it's author though that Hugo was saying exactly that, and that the standard has only two non-null values. Looking at Hugo's words again, I see that it probably does mean that and not the meaning I commented on. If so he and I agree that the type can't represent a three valued logic, but disagree that it's inappropriately named - it's a clean representation of a Boolean two-valued logic; and the idea that it could be used to represent the truth values of a three-valued logic is most defintely a cause to shudder hard, an utterly horrible confusion of levels: just the opposite of what my original comment said. To say that I know that a truth value is known and is neither TRUE nor FALSE is very different from saying that I don't know what that truth value is, because in a three valued logic there is only one value that is neither TRUE nor FALSE; in fact the two statements contradict each other.

I'll have to see if my local library can get me a copy of the standard, so that I can know for sure whether ANSI was that incompetent or not. I think the reason I originally interpreted Hugo's words as I did was that I didn't imagine that ANSI could have got it that wrong - far worse than a naming error. It is the sort of error that would be amazing if committed by someone who had taken just the first couple of lectures on teh varieties of logic used in mathematics, or even just read a 1 sheet of A4 introduction to constructive mathematics.

Tom

juliyasb
juliyasb
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 102
Would not + sign give error message?
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