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
Puzzle 1 – Answer – Result length too short to display
Puzzle 2 – Answer – select ASCII(”)
Puzzle 1- Answer – Result length too short to display
Puzzle 2- Answer – select ASCII(”)
select ISDATE(getdate())
Puzzle 1- Answer – Result length too short to display
Puzzle 2- Answer – select ISDATE(getdate())
select ASCII(”)
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer:
* (star) because the Result length too short to display.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Answer:
select COUNT(*)
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer:
5(They are Windows, MacOS, Linux, Solaris, Joyent SmartOS)
1. The size of the integer 634 is not matching with destination data type. Source has length 3 and destination is 2. So it is giving answer as *.
SELECTCAST(634 AS VARCHAR(1)) also return *
2. SELECT COUNT(*)
Bonus Qustn
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1:
Per Books On Line, “Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.” The table then goes on to show that int, smallint or tinyint values, if becoming too short to display manifest as “*”.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
Puzzle 2:
SELECT COUNT(*)
Bonus Q:
MacOS
Windows
RHEL,SuSe,Amazon Basic EC2
Ubuntu
Solaris
Joyent SmartOS
p1:If Target size is less than source size then it will show *
p2: select LEN(‘a’)
Hello Pinal, here’s my answers:
Puzzle 1: it returns * because the number is 3 digits long and the sql statement is trying to cast it as a varchar long 2.
Puzzle 2: select RIGHT(ASCII(”)),1)
Bonus Q: 5 OS
Have a nice day!
Puzle 1. Due to Truncation when converting int to varchar. This will throw error when converting to nchar, nvarchar
Puzzle 2. SELECT (SELECT 1))
Puzzle 2:
SELECT COUNT(‘A’)
the answer are as belows
Ans 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.
* = Result length too short to display
Ans2) select (count(*))
Answer for the first question :
You have to specify the correct length
SELECT CAST(634 AS VARCHAR(2))
It should have been VARCHAR(3)
So the query when rewritten : SELECT CAST(634 AS VARCHAR(3))
Answer for the second question :
Asked shortest query :
SELECT CAST(‘*’ as CHAR)
Bonus Question answer :
Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS
1)Answer
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
* = Result length too short to display. E = Error returned because result length is too short to display
2) Answer
select (count(*))
1. SELECT CAST(634 AS VARCHAR(2)) results in ‘*’ because Int is implicitly convertible to varchar and if the length to convert is less than the no of digits then it results as a warning .
* = Result length too short to display
2. SELECT (@@ROWCOUNT)
SELECT CAST(‘true’ AS bit)
SELECT CAST(‘true’ AS bit)
1. SELECT CAST(634 AS VARCHAR(2)) results in ‘*’ because Int is implicitly convertible to varchar and if the length to convert is less than the no of digits then it results as a warning .
* = Result length too short to display
2. SELECT (@@ROWCOUNT)
BonusQ:
OS Supported by NuoDB:
1. MacOS 10.7 or higher
2. Windows(32-bit, 64-bit)
3. RHEL 5 & 6 (64-bit)
4. SuSe 10 & 11 (64-bit)
5. Amazon Basic EC2 (64-bit)
6. Ubuntu 10 & 11 (64-bit)
7. Solaris 11 (Intel 64-bit)
8. Joyent SmartOS (Intel 64 bit)
Select CAST(‘True’ AS Bit)