SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1
- by pinaldave
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 (http://blog.SQLAuthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology Tagged: NuoDB
