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.
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Write the shortest code that produces results as 1 without using any numbers in the select statement.
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.
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.
The shortest code to produce answer 1 :
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.
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)
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.
Great Point. I have added the same in explanation.
Is print 1 not considered?
The conditions were – not to use any digit so 1 will not be considered – even SELECT 1 will not be considered.
Additionally, what is the fun if answer was just to display 1 using PRINT and SELECT. The trick was to display 1 without using 1.
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?
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
SELECT LEN(‘a’) and many other are for sure returning result but they are not shortest code when compared to SELECT EXP($). There are two extra characters in SELECT LEN(‘a’).
The answer should shortest length.
Agreed with your response, what Satya has just asked.
How about it if i have used below code to get 1.
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 ($).
Just correct me if i am wrong, would be more than happy to listen you.
SELECT COUNT(*) has 6+8=14 chars
SELECT EXP($) has 6+6=12 chars
The contest was of shortest code. Just like EXP, COUNT is also built in function and like $, * is also symbol. I think looking at this SELECT COUNT(*) is not a right answer but SELECT EXP($) is.
Again remember the contest was to write shortest code to display 1.
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’)