Earlier on this blog we had asked two puzzles. The response from all of you is nothing but Amazing. I have received 350+ responses. Many are valid and many were indeed something I had not thought about it.
I strongly suggest you read all the puzzles and their answers here – trust me if you start reading the comments you will not stop till you read every single comment. Seriously trust me on it. Personally I have learned a lot from it.
Let us recap the puzzles here quickly.
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Bonus Q:
How many different Operating System (OS) NuoDB support?
As I mentioned earlier the participation was nothing but Amazing. I will write about the winners and the best answers in short time. Meanwhile I will give to the point answers to above puzzles.
Solution 1:
When you convert character or binary expressions (char, nchar, nvarchar, varchar,binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.
Reference of the text and table from MSDN.
Solution 2:
The shortest code to produce answer 1 :
SELECT EXP($)
or
SELECT COS($)
or
SELECT DAY($)
When SELECT $ it gives us the result as 0.00 and the EXP of the same is 1. I believe it is pretty neat. There were plenty other answers but this was the shortest. Another shorter answer would be PRINT EXP($) but no one has proposed that as in original Question I have explicitly mentioned SELECT in the original question.
Bonus Answer:
5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOSÂ Reference
Please do read every single comment here. Do leave a comment which one do you think is the best comment out of all the comments. Meanwhile if there is a better solution and I have missed it do let me know as we still have time to correct it. I will be selecting the winner before the weekend as I am going through each and every of 350 comment.
I will be selecting the best comments along with the winning comment. If our selection matches – one of you may still win something cool.Â
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Though EXP and COS return the desired result, I think SELECT DAY($) is ‘more correct’ because that result returns an integer rather than a float.
Hi Pinal,
Is print 1 not considered?
So, what’s that $ thingie? Does sql server have access to my bank account, or how does it know that it’s zero?
Seriously, is that some kind of built-in variable or something?
SELECT ’1′
the reason for using ’1′ (and I got some slack for using ’1′) is because technically ’1′ is NOT a number it is a char! so select ’1′ does reproduce the requested output, and if you where using ado to set to int sql server likes to do something called implicit casting.
nice question though even if I do get some stick for my answer, I was also thinking of the print statement… Im up for anything that makes sql fun!
HI Pinal ,
Why not SELECT Len(‘a’) ? I hope this also can be right answer. If not pls explain me
Hi Pinal,
Agreed with your response, what Satya has just asked.
How about it if i have used below code to get 1.
Mine Code:-
SELECT COUNT(*)
I think, this is another shortest way to get 1, as in your code you have used built in function EXP with SELECT Statement for a special symbol Dollar ($).
Your code:-
SELECT EXP($)
Just correct me if i am wrong, would be more than happy to listen you.
Regards,
Girijesh
hi, when the results will be announced.. ?
Something unique but not not shortest one..
SELECT CONVERT (bit, ‘true’)
Not the shortest one but i guess unique one :-)
SELECT CONVERT (bit, ‘true’)
SELECT COUNT(getdate())
Another solution:
Print day($)