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:
* means Result length too short to display
Puzzle 2:
select exp(”)
Question 1: the result is too small for the size of the number therefore the cast fails and a ‘*’ is displayed as the result.
Question 2: declare @x bit=0; select ~@x
Hi Pinal:
Puzzle 1: The star (*) is displayed because the results is too large to fit – this is similar to Excel displaying ‘###’ in columns that are not wide enough to display the data.
Puzzle 2: select COUNT(*) is the shortest select to produce result of 1 without using numbers If needed, it can be select (COUNT(*)).
BONUS Q: 5
Thanks and have a great day,
Dave
Here is the query.
select len(‘a’)
Puzzle 1:
SELECT CAST(634 AS VARCHAR(2))
it returns * because the conversion result set is too low to return the entire result.
If we change the query to
SELECT CAST(634 AS VARCHAR(3))
then it will returns 634.
select (*)
Question 1: The type is to short for the conversion so Sql returns an asterisk.
Question 2:Select CAST(‘True’ as Bit)
1. Because converting 634 to varchar(2) would truncate string, then (by design) CAST returns ‘*’. however when try to cast it to varchar(3) – it would return 634
Next MSDN link shows that:
https://www.microsoft.com/en-us/download/details.aspx?id=51958
2. I think the shortest code that returns 1 would be
Select Len(‘a’)
Puzzle 1:
From BOL: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 under Truncating and Rounding Results for VARCHAR: * = Result length too short to display.
Puzzle 2:
SELECT LEN(‘.’)
A1. Result length too short to display.
A2. SELECT db_id(‘master’)
BA. Windows, MacOS, SmartOS, Solaris and Linux platforms.
Arithmetic overflow.
puzzle 2: SELECT LEN(‘A’);
select ASCII(‘A’)/ASCII(‘A’)
select count(*)
Puzzle1 Response:
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
Refer to “Truncating and Rounding Results”
Puzzle 2:
select CAST(ascii(‘o’) AS BIT)
Puzzle 1:
* is define in cast function and it appear if the result length too short to display.
in SELECT CAST(634 AS VARCHAR(2)) we are trying to cast a number of three length into two length of varchar,
SELECT CAST(‘634’ AS VARCHAR(2)) if we define it like this it will returns 63 because datatype is compatible and no need of explicit conversion.
Puzzle 2:
Select Len(‘A’)
Q1: because of Overflow, as you are trying to cast the 3 digit number on 2 letter varchar. If you use Varchar(3), you will get the 634 as answer.
Q2: SELECT LEN(‘A’)
1. As the length of the integer(682) is greater than specified in the Cast Function (i.e 2).
2. SELECT @@ROWCOUNT
OR SELECT ROWCOUNT_BIG()
3. NuoDB Support Windows, MacOS, Linux, Solaris, Joyent SmartOS
select len(‘a’)
For question 1, the reason is because the result is too large for the display size. In which case, the default response for SSMS is to display ‘*’ indicating that it does not have sufficient space to display the result due to the truncation on casting. To make that work, the value should first be cast to varchar with a size of at least 3, then cast again to varchar(2) OR using the SUBSTRING function to get the first two characters.
For question 2, there several options, but “SELECT ASCII(‘b’)-ASCII(‘a’)” is a very simple one.