SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1

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)

About these ads

17 thoughts on “SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1

  1. 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.

    • 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.

  2. 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?

  3. 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.

  4. 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!

    • Satya,

      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.

  5. 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

    • 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.

  6. Pingback: SQLAuthority News – Amazon Gift Card Raffle for Beta Tester Feedback for NuoDB « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s