We all love brain teasers and interesting puzzles. Today I decided to come up with 2 interesting puzzles and winner of the contest will get USD 50 worth Amazon Gift Card. The puzzles are sponsored by NuoDB. Additionally, The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
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?
Click here HINT
If you can solve above puzzles you will be eligible for winning USD 50 Amazon Gift Card. However, you can always enroll yourself for following Bonus Prizes where if you have good chance of winning USD 10 Amazon Gift Card (if you are first 25 individual in specific time).
Bonus Prizes:
The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
Rules:
- Please leave an answer in the comments section below.
- You can resubmit your answer multiple times, the latest entry will be considered valid.
- The winner will be announced on 1st October.
- Last day to participate in the puzzle is September 28th, 2012.
- All valid answer will be kept hidden till September 28th, 2012.
- Only One Winner will get USD 50 worth Amazon Gift Card.
- The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
- The winner will be selected using random algorithm.
UPDATE: The winner of the contest is Jaime Mtnz Lafargue (@jaimeml)
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
365 Comments. Leave new
Because varchar(2) isn’t large enough to hold the characters that make up 634.
select COUNT(‘answer’)
Puzzle1:
in “Select cast(634 as varchar(2))” we are trying to convert 3 digits to 2 char. This overflows the target string which is shown as “*” in SQL server.
At least Varchar(3) is required to display the number. Even Varchar(1) with this number will return “*”
Puzzle2:
Select len(‘a’)
will return 1
Bonus Q:
NuoDb supports 6 different Os
Hi, just saw your contest on LinkedIn. Very nice of you to sponsor. This is the best that I came up with. It can’t get too much shorter :-)
select len(‘a’)
Thanks!
Puzzle 1 —> Since 634 contains 3 digits, casting it to varchar also contains 3 digits. However, we have restricted the result length to 2. So the result length is too short to display the actual result. Hence an ‘*’ is displayed. If we increase the length to 3 or more or ommit the length (which will by default take 30 characters), it will display the proper output of 634.
Puzzle 2 —-> select len (‘a’)
You can select length of any single non-numeric character.
Puzzle 1:
It is becuase the size of the value specified(2) is not enough to hold the number(634). In this case you wont get any error.
You should in advance specify the enough length minimum 3. SELECT CAST(634 AS VARCHAR(3))
Puzzle 2:
SELECT CHARINDEX(‘M’,’M’)
My answers
Puzzle 2:
select cast (‘True’ as bit)
Puzzle 1:
* is displayed in the result set if Result length too short to display.
Another solution to Puzzle 2:
select len (‘x’)
Select (ASCII (‘b’)-ASCII(‘a’))
select Len(‘a’)
Answer 1:
When converting 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.
* Result length too short to display.
E Error returned because result length is too short to display.
Answer 2:
select len(‘a’)
1.) Whenever trying to convert character or binary expressions ( like 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 but except for some data types like here int /small int to char/Varchar . Then it finally returning *.
2.) select Len(‘a’) ,it returns the 1 value
3.) 5 types of OS ( Windows , Linux, MacOS , Joyent SmartOS , Solaris)
My second entry for puzzle 2: SELECT ‘1’ — technically ‘1’ is a character and not a number, just like ‘A’ :-)
Q1. * = Result length too short to display.
Q2. SELECT LEN(‘a’)
for first question, as we mention varchar (2) to typecast 3 digit number
for second question , select len(‘a’)
Q2. SELECT cos($)
Puzzle 1
–> Because [SELECT CAST(634 AS VARCHAR(2))], here length of ‘634’ is 3 whereas we casted numeric value to 2 length varchar which exeeded.[SELECT CAST(634 AS VARCHAR(3))] would give 634 as output.
Puzzle 2
select COUNT(‘A’)
Bonus Q
NuoDB supports for 5 OSs Windows, MacOS, Linux, Solaris, and Joyent SmartOS
Puzzle 1: 634 has 3 characters and cannot be converted by SQL to 2 chars. * means error
Puzzle 2: SELECT (ASCII(‘B’)-ASCII(‘A’))
select len(‘a’)
The answer to your query is: “Historical reasons”
When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).
The datatypes INT and VARCHAR are older than BIGINT and NVARCHAR. Much older. In fact they’re in the original SQL specs. Also older is the exception-suppressing approach of replacing the output with asterisks.
Later on, the SQL folks decided that throwing an error was better/more consistent, etc. than substituting bogus (and usually confusing) output strings. However for consistencies sake they retained the prior behavior for the pre-existing combinations of data-types (so as not to break existing code).
So (much) later when BIGINT and NVARCHAR datatypes were added, they got the new(er) behavior because they were not covered by the grandfathering mentioned above.