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:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer 1:
* Result length too short to display.
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.
E Error returned because result length is too short to display.
Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release
we can do roundtrip converion for: select cast(CAST(63 AS VARCHAR(2)) as int)
but not for : select cast(CAST(635 AS VARCHAR(2)) as int)
so sql server will show * because of its too short to display
–=================================================================================================
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
select (your answer)
Answer 2:
select ascii(”)
OR
select count(*)
–=================================================================================================
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer BonusQ:
MacOS
Windows
Linux (RHEL,Ubuntu,SuSe,Amazon Basic EC2)
Solaris
Joyent SmartOS
Puzzle 1: * Result length too short to display.
Puzzle 2: SELECT DATEPART(m,12-MONTH(GETDATE()))
Hi Pinal,
Answer for puzzle 1:
SELECT CAST(634 AS VARCHAR(2))
While you are casting a number value to varchar(2), so depending upon the
length of number, its not able to cover its complete length as you are casting it to varchar.
Lets take an example
select CAST(12341234 as varchar(1))
will give you * in result. This will give you * untill we are casting it upto varchar(7). Please note that length of number is 8. Now take another example:
select CAST(12341234 as varchar(8))
This will return the complete number – 12341234.
So in puzzle 1 – As per my example above
SELECT CAST(634 AS VARCHAR(2)) or SELECT CAST(634 AS VARCHAR(1))
will give * in result. While
SELECT CAST(634 AS VARCHAR(3)) will give complete number
In Puzzle1 data is truncated so ‘*’ is displaying. SQL guarantees that only roundtrip conversions are allows.
Answer Puzzle 1- When we Cast (int, smallint or tinyint) to an expression of a different data type (char, varchar) data can be truncated, only partially displayed because the result is too short to display. Here the length of 634 is 3 and target varchar type is 2 thats why partially result is displayed as ‘*’.
Answer Puzzle 2- select len(‘a’)
SELECT LEN(‘_’)
what about my answers Sir………
Hi
Puzzle 1 answer
This is because vachar size is 2 instead of 3.
Casting vice-versa must also work but that will not work in this case.
SQL here is not able to cast * to 634 which is violation casting rule.
Hence SQL is returning * .
Regards,
Ans.1 : It is happening due to varchar size. It should be increased as 3, to get the number as output “634”.
Ans.2 : Select count(*)
Ans.3 : NuoDB supports all Industry leading operating system.
Hello Pinal,
For display 1 without numbers. In general any short name function that return int, float ,etc will be divide by himself.
select -cos(Pi())
select Sign(Pi())
select Pi()/Pi()
Puzzle 1: Displayed result is an asterisk because converted number is too large to fit in excected data type. (*) in result means – “Result length too short to display”.
Puzzle 2: Shortest code I am able to come up with is:
SELECT COUNT(*)
Bonus Q: NuoDB supports 5 different families of operating systems: Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Answere for Puzzle 1:
634 is three digit Number casting by varchar(2) it will show * because Of Less Size of VARCHAR
i mean by casting 634 we use varchar(3) (because of three digit)
other wise it will show * in the result
the casting number(634) length must Less than or equal to varchar size
other wise it show * as result
Answere for Puzzle 2:
SELECT CAST(‘true’ as Bit)
Hello Pinal,
Better solution len(‘A’). Only 8 character
Puzzle 1:- Because the varchar(2) is not sufficient to accomodate the width of 3 digit no.
Puzzle 2:- Select len(‘a’)
Ans-1-it is show * becuase length of varchar data type is 2.
Ans-2-
SELECT ‘a’+CAST( COUNT(‘abc’) AS VARCHAR(1))
Answer for puzzle 2-
select (char(49))
This will return result 1 as expected.
Answer for puzzle 1:
SELECT CAST(634 AS VARCHAR(2))
While you are casting a number value to varchar(2), so depending upon the
length of number, its not able to cover its complete length as you are casting it to varchar.
Lets take an example
select CAST(12341234 as varchar(1))
will give you * in result. This will give you * untill we are casting it upto varchar(7). Please note that length of number is 8. Now take another example:
select CAST(12341234 as varchar(8))
This will return the complete number – 12341234.
So in puzzle 1 – As per my example above
SELECT CAST(634 AS VARCHAR(2)) or SELECT CAST(634 AS VARCHAR(1))
will give * in result. While
SELECT CAST(634 AS VARCHAR(3)) will give complete number
Answer for puzzle 2-
select (char(49))
This will return result 1 as expected
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer – Five OS
1. 634 is identified as an integer by SQL Server and Asterisk is the result when an integer is converted to a varchar, char, nvarchar or nchar and truncated.
2. SELECT len(‘a’)
3. 5 (Windows, MacOS, Linux, Solaris, Joyent SmartOS)
Puzzle 1:
Ans: If Result length is too short to display, then result would be *
Puzzle 2 :
Ans: SELECT (@@ROWCOUNT)
Bonus Q
Ans: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1:
Answer:-Simply as we all known Cast() function is used for data conversion.When the cast() fails or whlie the conversion data can be truncated,partially displayed or an error is returned because the result is too short to display.When we convert “int/tinyint/bigint” to “char” or “varchar” and in that case if the cast() fails then it returns “*”.Here the “*” indicates that the conversion happened with an incorret value which is too short to display.
SELECT CAST(634 AS VARCHAR(2))
In above SELECT statment, for the value 634 to be converted successfully to varchar type variable , 634 needs 3 bytes as 1 byte for each digit to be stored as character. But in above statement the target expression is of type varchar(2) which can store only two bytes. So the value 634 can not fit into box of varchar(2) and results in incorrect resulting value i.e. “*”.
In above SELECT statement, any expression which requires more than 2 characters to represent the value will not be converted successfully to varchar(2) and returns “*”.
In other words, only 1 or 2 digit values or values between 0 to 99 can be converted and if you want to convert 634 you have to use varchar(3) or greater or you can simply use “varchar” instead of “varchar(max)” means their is no requirement of providing length with target expression i.e. “varchar”.
e.g-SELECT CAST(634 as VARCHAR)
Puzzle 2:
Answer:-SELECT COUNT(*)
Bonus Q:
Answer:-Windows, MacOS, Linux, Solaris