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
Hi Pinal,
Please find below my answers:
Puzzle 1:
* is displayed because varchar(2) is not sufficient to display the result after applying cast operator to 634.Result length is too short.
Puzzle 2:
select ascii(‘B’)-ascii(‘A’)
Puzzle 2:
Ans :select (@@ROWCOUNT)
It returns always 1
Puzzle 1:
After executing of the code “SELECT CAST(634 AS VARCHAR(2)) ” displays * (star) because
“Result length too short to display.”
Puzzle 2:
Ans :select (@@ROWCOUNT)
It returns always 1
HI Pinal,
The answer for the Bonus question i.e. How many different Operating System (OS) NuoDB support?
These four (Windows, MacOS, Linux, Solaris) OS support NuoDB
Thanks
Manish
Solution of Puzzle 1:
When we convert NUMERIC data type( int , smallint, or tinyint) value to string data type(char or varchar) of length less than the digit counts in numeric value, then we will get ‘*’ as a result value. Here we are truncating the numeric value and sql server will show ‘*’ in result to indicate that result value length is too short to display.
Like in this example if i convert the same int value(634) into varchar having length 3 or more i will get the same number in the result.
SELECT CAST(634 AS VARCHAR(2)), CAST(634 AS VARCHAR(3)), CAST(634 AS VARCHAR(4)), CAST(634 AS VARCHAR)
Output :
(No column name) (No column name) (No column name) (No column name)
* 634 634 634
another example using different integer value.
SELECT CAST(5555 AS VARCHAR(3)), CAST(5555 AS VARCHAR(4)), CAST(5555 AS VARCHAR(5)), CAST(5555 AS VARCHAR)
Output:
(No column name) (No column name) (No column name) (No column name)
* 5555 5555 5555
Solution of Puzzle 2:
Select (LEN(‘d’))
Answer of Bonus Q:
NuoDB support for Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Thanks,
Ashish
Puzzle 2:Select len(‘/’) will return 1 without entering the number in the query.
Windows, MacOS, Linux, Solaris, Joyent SmartOS
supported by NuoDB
———————————————–
Puzzle. 1
———————————————–
It is due to data overflow while conversion.
SELECT CAST(634 AS VARCHAR(3)) works fine.
Any number with lower varchar cast will generage the same.
Eg. SELECT CAST(444 as VARCHAR(1))
SELECT CAST(111 as VARCHAR(2))
———————————————–
Puzzle. 2
———————————————–
SELECT LEN(‘.’)
Length of any Single char will generate 1.
For Puzzle2 Correct answer is — SELECT LEN(‘A’)
Bonus Question: How many different Operating System (OS) NuoDB support?
Answer: The following different types are supported operating systems
MacOS 10.7 or higher
Windows (32-bit, 64-bit)
RHEL 5 & 6 (64-bit)
SuSe 10 & 11 (64-bit)
Amazon Basic EC2 (64-bit)
Ubuntu 10 & 11 (64-bit)
RHEL 5 & 6 (64-bit)
SuSe 10 & 11 (64-bit)
Amazon Basic EC2 (64-bit)
Ubuntu 10 & 11 (64-bit)
Solaris 11 (Intel 64-bit)
Joyent SmartOS (Intel 64 bit)
1. Because the width provided in the data type is less than the length (3) of the value 634. If you can use varchar(3) or varchar then you will get 634 as the value.
select cast(123 as varchar(2)), cast(123 as varchar)
2. select LEN(‘a’) will give the required result.
1. Because the width provided in the data type is less than the length (3) of the value 634. If you can use varchar(3) or varchar then you will get 634 as the value.
select cast(123 as varchar(2)), cast(123 as varchar)
2. select LEN(‘a’) will give the required result.
Bonus Q:
How many different Operating System (OS) NuoDB support?
ANS: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 2 answer is:
SELECT LEN(‘Z’)
Bonus question: How many different Operating System (OS) NuoDB support?
Answer: following mentioned operating system are supported by NuoDB
MacOS 10.7 or higher
Windows (32-bit, 64-bit)
RHEL 5 & 6 (64-bit)
SuSe 10 & 11 (64-bit)
Amazon Basic EC2 (64-bit)
Ubuntu 10 & 11 (64-bit)
RHEL 5 & 6 (64-bit)
SuSe 10 & 11 (64-bit)
Amazon Basic EC2 (64-bit)
Ubuntu 10 & 11 (64-bit)
Solaris 11 (Intel 64-bit)
Joyent SmartOS (Intel 64 bit)
1. Length of the target datatype is restricted and not enough to display the result.
2. select CAST(‘true’ as bit)
BQ. 5 Operating Systems
PUZZLE 1: Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer: In the above query We are trying to convert 634(3 digit number) into varchar(2)(2 digit value). When we are casting any value(int/smallint/tinyint) as varchar/char type, if the length of converting value is shorter than casting value, result will be displayed as *.
* indicates Result length is too short to display
Bonus Q: Windows, MacOS , Linux and now Sun Solaris also supports NuoDB.
Puzzle 1 Answer:-
In this, we are converting “634” into varchar(2). Varchar take 1 byte for every character. Varchar(2) is to shot to display the converted data, so it’s show ” * ” according to msdn (“http://msdn.microsoft.com/en-us/library/ms187928.aspx”).
If we will convert this into varchar(3) then it will show “634”.
Puzzle 2 Answer:-
SELECT CHARINDEX (‘Y’,’Your Answer Here’)
Bonus Q Answer:-
NuoDB supports 6 Operating Systems
Puzzle 1:
Because SQL Server don’t want to forget about history ;). Yes in being that was a method to display an error of arithmetic flow.
Puzzle 2:
SELECT LEN(‘A’)
correcting my last query for printing 1 as output . u dnt need @@error also
just use
select @@rowcount
returns 1
Hi Pinal,
Welcome to brain teaser Puzzles…!!
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans : When integers are 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 (*). But in the case of converting Character Data to Character datatype data are truncated.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Ans:
SELECT DIFFERENCE(‘Ant’, ‘Elephant’)
SELECT DATALENGTH(‘ ‘)
SELECT LEN(‘x’)
SELECT ISNUMERIC($)
SELECT ISNUMERIC(‘+’)
SELECT ISNUMERIC(‘-‘)
SELECT ISNUMERIC(‘.’)
SELECT COUNT_BIG(*)
SELECT COUNT(*)
SELECT CEILING(RAND())
SELECT ISDATE(GETDATE())
SELECT MONTH(GETDATE())/MONTH(GETDATE())
SELECT DAY(GETDATE())/DAY(GETDATE())
SELECT YEAR(GETDATE())/YEAR(GETDATE())
SELECT POWER($,$)
SELECT ISNULL(NULL,COUNT(*))
SELECT LEN(NEWID())/LEN(NEWID())
SELECT @@SPID/@@SPID
SELECT UNICODE(‘A’)/ UNICODE(‘A’)
SELECT ISNUMERIC(@@SPID)
Regards,
Nikhildas
Cochin