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.

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

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($)

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

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

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

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)

Previous Post
SQLAuthority News – Follow up on – Replace a Column Name in Multiple Stored Procedure all together
Next Post
SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video

Related Posts

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.

    Reply
  • Hi Pinal,

    Is print 1 not considered?

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

      Reply
  • 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?

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

    Reply
  • 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!

    Reply
  • HI Pinal ,
    Why not SELECT Len(‘a’) ? I hope this also can be right answer. If not pls explain me

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

      Reply
  • Girijesh Pandey
    October 3, 2012 8:30 pm

    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

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

      Reply
  • hi, when the results will be announced.. ?

    Reply
  • Something unique but not not shortest one..
    SELECT CONVERT (bit, ‘true’)

    Reply
  • Not the shortest one but i guess unique one :-)
    SELECT CONVERT (bit, ‘true’)

    Reply
  • SELECT COUNT(getdate())

    Reply
  • Another solution:
    Print day($)

    Reply

Leave a Reply

Menu