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
* indicates “Result length too short to display”
Puzzle 2
select (1)
Bonus Q supports 5 different Operating systems
Q1: The * is displayed since the cast would result on truncation from an INT to a VARCHAR that can not accommodate all the digits..
Q2:
SELECT COUNT(*) FROM (SELECT ” A) C
Cheers, Pinal!
Question 1: Why “select cast(634 as varchar(2))” will give (*)?
Answer 1: the reason behind this is that in the very old versions of SQL Server, the impliciting conversions used to be handled resulting some values (* in this case) not the error message for some cases like if we try to convert the 634 to varchar(2), then logically it should give me result (‘634’) but here we have varchar(2) which can not hold 3 characters so it should have been given the error message of over flow but as the int is very old or Rather I would say very first datatype to be desined in numeric integers category so the working is still same and didnot get updated as bigint etc.
Below examples shows how this statement might have been worked:
select cast(634 as varchar(3)) would have given ‘634’
select cast(6340 as varchar(4)) would have given ‘6340’
select cast(6340 as varchar(3)) would give (*).
so this is the reason that our statement “select cast(634 as varchar(2))” is giving (*)
————————
Question 2: How to Write the shortest code that produces results as 1 without using any numbers in the select statement?
Answer 2: A use of deterministic system defined function will give the desired result as below.
select ASCII(‘B’) – ASCII(‘A’)
OR
select ISDATE(GETDATE())
or many more…
Thanks for this challange and would welcome some more in future for good hands on on SQL Server.
Nimit Goyal
(goyal.nimit@gmail.com)
facebook(https://www.facebook.com/nimit.goyal)
linkedin(http://www.linkedin.com/profile/view?id=98426239&trk=tab_pro)
SELECT count(‘q’)
The field length is too short to display. If you expand to 3 or larger presto you get your number. If you cast from a string you will see 63 as your return.
SELECT len(‘q’)
puzzle #2 :
select difference(‘Pinal’,’Dave’)
Ans 1. becoz we are making Attempt to convert 3 digit int to two alphabet varchar which is resulting in truncation
Ans 2. Select (1)
1-
SELECT 1,CAST(634 AS VARCHAR(2))
the result is * because the result is too short to display then the truncation algorithm returns *
2-
select (@@ROWCOUNT)
Bonus Q-
support 5 different OS
Puzze 1: Because it converts int type to varchar type and it has truncation due to the length of varchar is shorter than the number of digits in the integer. Hence, * means result length too short to display
(Ref:
Puzze 2: SELECT COUNT(*)
Bouns Q: 5 (Windows, MacOS, Linux, Solaris, Joyent SmartOS)
1) Result length too short to display due to truncation during the cast is the reason why the * is shown.
2) SELECT(ISNUMERIC(‘ ‘) – ~ISNUMERIC(‘ ‘))
Bonus) 5
ms sql server 2008r2
1) because 634 consist of 3 digits, but varchar(2) can cast only 2
2)
select(select count(*)x from(values((null)))n(sp))
select(select ascii(‘b’)-ascii(‘a’)x)
select(select object_id(‘sys.all_objects’)/object_id(‘sys.all_objects’))
select(select len(‘a’)x)
select(select cos(datediff(dd,getdate(),getdate())))
Answer for Puzzle 1: The length of the integer value is greater than the converting value that time it is showing the result.
Answer for Puzzle 2: 1
Answer of Bonus Q:NuoDB supports Windows, MacOS, Linux, Solaris, Joyent SmartOS operationg systems.
Hi Pinal,
Puzzle 1:
1) Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
A) If the length of integer value is less than the size of varchar in the conversion method, then it displays star.
For example,
SELECT cast( 256 as varchar(1))
SELECT cast( 256 as varchar(2))
SELECT cast( 256 as varchar(3)) — displays actual integer value
SELECT convert( varchar(3), 1234)
SELECT CAST(634 as varchar(2))
SELECT CAST(634 as varchar(1))
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
A) SELECT COUNT(*)
Hi Pinal,
Puzzle 1:
1) Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
A) If the length of integer value is less than the size of varchar in the conversion method, then it displays star.
For example,
SELECT cast( 256 as varchar(1))
SELECT cast( 256 as varchar(2))
SELECT cast( 256 as varchar(3)) — displays actual integer value
SELECT convert( varchar(3), 1234)
SELECT CAST(634 as varchar(2))
SELECT CAST(634 as varchar(1))
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
A) SELECT COUNT(*)
Bonus Q: How many different Operating System (OS) NuoDB support?
A) NuoDB supports for Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1: It is since casting is exceeding its limit of two characters. It can cast 0-99 in varchar(2) but if try cast three digit number into varcar(2) it can not cast it and gives you * as result.
Puzzle 1 : Its because of Length Overflow
Puzzle 2 : select (COUNT(*))
Puzzle 1
When you convert character or binary expressions(char,nchar etc) to an expression of a different data type,
1)data can be truncated
2)only partially displayed or
3)an error may return
Because the result is too short to display.
In the Puzzle 1
SELECT CAST(634 AS VARCHAR(2))
You are converting for int data type to varchar which results in ‘*’
Meaning – The result length is too short to display
Puzzle 2
The answer which results 1 in the answer other than using number is given below:
SELECT @@ROWCOUNT
Bonus Q
NuoDB supports 5 different OS
1) Windows
2) MacOS
3) Linux
4) Solaris
5) Joyent SmartOS
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans : Since VARCHAR(2) is Mentioned in the Code, the Result is * (Star).
Which indicates THE RESULT LENGTH TOO SHORT TO DISPLAY
If You Change the to SELECT CAST(634 AS VARCHAR(3)) then It will work fine.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
SELECT (YOUR ANSWER HERE)
Ans : SELECT LEN(‘A’) will ouput as 1.
Very Good Puzzles Sir.. I enjoyed with this.
Thanks Lot and Expecting still more Puzzles from Your side.
1. Because varchar(2) is specified and int is of 3 digits. Hence it converts all numbers more than 100 to default value *.
2. SELECT CAST(GETDATE() AS INT)/CAST(GetDate() AS INT)
Bonus Q. Windows, MacOS,RHEL 5 & 6, SuSe 10 & 11 ,Amazon Basic EC2, Ubuntu 10 & 11, Solaris 11, Joyent SmartOS