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
Ans: Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.
Puzzle 2
Ans: SELECT len(‘a’)
when the output length exceeds the space provided, SQL resorts to shorthand
Quiz 2:
SELECT len(‘A’)
Quiz 1:
Varchar(2) hold maximum of 2 characters.
So SELECT CAST(99 AS VARCHAR(2)) will show 99 and any number above 99 will show * because converting character (char, nchar, nvarchar, varchar) 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.
converting 634 to varchar(2) is displayed as * because * means “Result length too short to display”
Puzzle #1 answer: Truncated output as a result of conversion between int/smallint/tinyint and char/varchar will result in an output of an asterisk.
Puzzle #2 answer: select len(‘a’)
Bonus answer : NuoDB supports 5 operating systems.
Puzzle 1 -> SELECT CAST(634 AS VARCHAR(2))
Answer—->
The output ‘*’ is showing the data overflow while conversion(high to low precedence conversion). Problem here is we have data value(i.e. 634) having more length then the defined one which is varchar(2) in above statement.
This can be fixed as
SELECT CAST(634 AS VARCHAR(3))
Puzzle 2 –> shortest code that produces results as a1 without using any numbers in the select statement
Answer –> SELECT len(‘a’)
Ans 1: We are casting 3 digit number to varchar(2) which is going out of bound thats why its giving *, if we increase it to varchar(3) it will shoe correct digit or if we pass any digit upto 99 it will show correct results. When we are converting this to varchar it is making them as three char but it can only hold 2 .
Ans 2: Select CAST(‘True’ as bit)
Here are my answers for the puzzles :
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer :
The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.
Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’
Puzzle 2:
Write the shortest code that produces results as a1 without using any numbers in the select statement.
Method 1
SELECT (‘a’ + CAST(COUNT(*) AS VARCHAR(1))) AS [Result]
Method 2
SELECT (‘a’ + CAST(COUNT(”) AS VARCHAR(1))) AS [Result]
Looks like Puzzle 2 has been updated from a1 to 1 in the question.
so I’m updating my answers here.
Here are my answers for the puzzles :
Puzzle 1:
——————
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer :
——————
The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.
Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’
Puzzle 2:
——————-
Write the shortest code that produces results as a1 without using any numbers in the select statement.
Answer :
——————
Method 1
SELECT COUNT(*)
Method 2
SELECT COUNT(”)
1) as part of the rule “truncating and rounding result” its showing *. ideally from -9 to 99 will come fine but other than that range it will display a *.
2) select ascii(‘B’)-ascii(‘A’)
1) as part of the rule “truncating and rounding result” its showing *. ideally from -9 to 99 will come fine but other than that range it will display a *.
2) select ascii(‘B’)-ascii(‘A’)
1. The data was truncated and * means Result length too short to display.
2. SELECT LEN(‘A’)
Bonus: 5
Question 1 : Number characters exceed varchar character limit
Question 2 : SELECT COUNT(GETDATE())
Question 1: Character exceed Data-types limit hence the start
Question 2: SELECT COUNT(GETDATE())
Ans 1: We are storing 3 digit number and the space allocated is only for 2 digits hence it cannot accomodate it so it shows the result as 0
Ans2: Select COUNT(”) would return the value as 1
Puzzle 1:Varchar support the integer from 0-99 for casting into the varchar datatype ,but if it exceeds from 99 to any 9 digit length number ,it shows always * at the type of conversion.
Ans 1: We are storing 3 digit number and the space allocated is only for 2 digits hence it cannot accomodate it so it shows the result as ‘*’
Ans2: Select COUNT(”) would return the value as 1
Answers :
Puzzle 1 :
Number of digits specified in casting is 3 its greater then value specified in data type. [varchar(2)]. So that its result as a * . If you change the datatype value as varchar(3) then you got the result as 634.
Puzzle 2 :
SELECT (SELECT database_id FROM SYS.DATABASES
WHERE name = ‘master’)
I am not sure its correct way to get result as 1 without using numeric. But, my thinking its also one of the way to get result as 1 using this query.
Bonus Q :Its supports 5 Operating Systems. They are :
Windows, MacOS, Linux, Solaris and Joyent SmartOS
Looks like Puzzle 2 has been updated from a1 to 1 in the question.
so I’m updating my answers here.
Here are my answers for the puzzles :
Puzzle 1:
——————
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer :
——————
The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.
Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’
Puzzle 2:
——————-
Write the shortest code that produces results as a1 without using any numbers in the select statement.
Answer :
——————
Method 1
SELECT COUNT(*)
Method 2
SELECT COUNT(”)
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer :
——————
06
For getting 1 as output without using numeric value i can use following sql global variable :
select @@error+@@rowcount
following query will give 1 as output .
2nd method for printing 1 : we can find out the difference of two consecutive ascii value no.
select ASCII (‘b’)-ASCII (‘a’)
This will also return 1
Hi Pinal,
The result of Puzzle No. 1 is * because of Truncating and Rounding Results information from MSDN https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
the result of the following query
select CAST(634 as varchar(2)) is = *
because * = Result length too short to display.
Here we are casting three digit value to another data type of 2 bytes, but if we do something like this select CAST(634 as varchar(3)) , this will work properly and display 634.
Thanks
Manish