COALESCE
-
Comments posted to this topic are about the item COALESCE
-
gosh.. I almost overlooked the quoted null ..:w00t:
nice question....:Whistling:
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
Nice question. Luckily have not fallen into "Quoted Null" trap 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Nice question! learnt something new Today:-) Thank You!
-
good start of the day with +1..
first did R&D about coalesce thn attempt the question and select correct answer 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Great question. Had to read it a few times to figure out in what order the functions were called.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
To me this feels more like a trick question than a question about coalesce. The trick was to notice that 'NULL' was quoted and not the NULL value. A valid point, but nothing to do with coalesce.
My other gripe is that it doesn't actually use coalesce in a sensible manner. One of the things about coalesce is that you don't have to nest them as it will work through until it finds a non-NULL value. If the question was really about coalesce and not isnull then I think the query could (and should?) have been written as
Declare @a varchar(100)
select @a = null
select @a = coalesce('NULL','NA',@a,'Pass')+'Fail'
select @a
Philip
-
philip.cullingworth (10/17/2012)
To me this feels more like a trick question than a question about coalesce. The trick was to notice that 'NULL' was quoted and not the NULL value. A valid point, but nothing to do with coalesce.My other gripe is that it doesn't actually use coalesce in a sensible manner. One of the things about coalesce is that you don't have to nest them as it will work through until it finds a non-NULL value. If the question was really about coalesce and not isnull then I think the query could (and should?) have been written as
Declare @a varchar(100)
select @a = null
select @a = coalesce('NULL','NA',@a,'Pass')+'Fail'
select @a
Philip
Very true as COALESCE function takes the first "Not NULL" value from the list of options. In case, we want to play around with the sequence, nesting of the funtion become very handy.
Furthermore, Here is the correct sequence for with the COALESCE boils down to 🙂
select @a = coalesce(@a,'NULL','NA','Pass')+'Fail'~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
But the query that you pasted is just simply give the first non null value as the coalesce does and give output as 'NULLFail'.... 😛 (no tricks in it)
today's QOD is little tricky to make confusion between null and 'NULL'
I am not sure about your statement regarding nesting of coalesce but I think we can do that...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Lokesh,
Thanks for correcting my mistake. What comes of trying to write a response before the coffee has kicked in.
Philip
-
Koen Verbeeck (10/17/2012)
Great question. Had to read it a few times to figure out in what order the functions were called.+1
Thanks
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
I was almost hesitant to answer the question, thinking there might be a trick thaht I was overlooking. Fortunately, I wasn't.
The explanation is wrong, though. The innermost COALESCE does NOT return "a typed NULL", but a non-NULL string value (that just happens to be equal to the string 'NULL'). And since this is a regular, non-NULL value, all other COALESCE and ISNULL calls simply let it bubble up.
Just for the record: the term "a typed NULL" does not mean (what the author of the question apparently thinks) "the word NULL, just typed in by me on my keyword". It means "a NULL value with data type association". This is contrast with an untyped NULL, which, obviously, is a NULL value with no data type association.
The constant (without quotes) NULL is technically an untyped NULL. At some point, SQL Server will convert this to a typed NULL, choosing the data type based on context. If I execute "SELECT NULL + 1, NULL + 'Text', DATEADD(day, 12, NULL);", the three untyped NULLS will be converted to respectively integer, varchar(??), and one of the date/time types. If you just execute "SELECT NULL;", SQL Server will have to choose on a data type before sending it to the client, because the protocol for communication between SQL Server and the client doesn't support untyped data. I think the choice will be integer, but I'm not sure.
A typed NULL can be achieved by using a CAST or CONVERT expression. For instance CAST(NULL as varchar(20)) is a NULL, typed as varchar(20). One way to see this in effect is to set SSMS to use "output as text", and then execute for instance "SELECT CAST(NULL as int), CAST(NULL as varchar(4)), CAST(NULL AS numeric(15,2)), CAST(NULL AS float), CAST(NULL AS datetime2);". Each of the output columns has a different length, based on the data type of the column.
Most of the time, there is no practical difference between using typed or untyped nulls. But someimes, you do need to use a NULL value, and the default choice SQL Server makes when choosing its data type is wrong - in that case, using an explicitly typed NULL is the only option.
PS: Sorry for going off on a tangent... None of the above is even remotely related to the question (though it is somewhat related to the mistake in the explanation).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Nice question. Don't see a trick in it as long as NULL is within quotes. However, explanation is absolutely incorrect:cool:
Sujeet Singh -
demonfox (10/16/2012)
gosh.. I almost overlooked the quoted null ..:w00t:nice question....:Whistling:
I did overlook the quotes on the quoted 'NULL', so I got it wrong.
Nice question.
Probably the worst explanation I've ever seen - two gross mistakes, which unfortunately cancel each other out so that people might be misled into believing them. It starts with the bizarre idea that coalesce called with two non-null arguments returns null, and ends with the idea that a typed null ehen concatenated with a string magically turns from null to 'NULL'! That definitely needs fixing.
Tom
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply