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
Answer for the first puzzle is here:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms187928(v=sql.90)
in “Truncating and Rounding Results” section.
Answer for the second puzzle is:
SELECT ASCII(‘b’) – ASCII(‘a’)
Here are my answers:
1. When integers are implicitly converted to a varchar data type, if the integer is too large to fit into the varchar datatype field, SQL Server enters ASCII character 42, the asterisk (*).
2. we have some mathematical functions. I picked cos and exp. All of these statements return 1 as a result:
select cos($) — dollar
select exp(£) — British pound
select cos(â‚¡) — Costa Rican colón
select exp(â‚¡)
select cos(₦) — Nigerian naira
select exp(₦)
select cos(Â¥) — Japnese Yen
select exp(Â¥)
To be honest, you can use almost any currency sign in cos or exp function and it will return 1. The thing is that currency sign is treated like a numeric value:
eg: select isnumeric(â‚¡) returns 1 and select $ returns 0.00. So at cos($) and exp($) we have value equal to one as these functions represent 1 at a zero point.
3. BONUS Q:
5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1: Overflow, 634 is longer than 2 characters, so SSMS displays a *
Puzzle 2: SELECT count(*)
Bonus Q: Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS
Hi Pinal,
You rock. These are really teasers.
Puzzle 1: under MSDN, result of conversions from int, smallint, or tinyint to char or varchar can be * which means “Result length too short to display”
Puzzle 2: SELECT(PI()/PI())
Bonus Q: Windows, MacOS, Linux, Solaris, Joyent SmartOS
select 1
select len(‘A’)
This is documented in the Truncating and Rounding Results section of CAST and CONVERT in Books Online. The * means ‘Result length too short to display’. This odd behaviour is maintained for backward compatibility with old versions of SQL Server. The more modern types nchar and nvarchar return an error instead:
SELECT CAST(643 AS nvarchar(1));
SELECT CAST(45 as nchar(1));
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.
Hi Pinal,
Ans:
Puzzal 1
SELECT CAST(634 AS VARCHAR(2)) Query
When converting INT to CHAR/VARCHAR If result length is too short to display (ie: VARCHAR(2) in our query) by default it display *.If it is SELECT CAST(63 AS VARCHAR(2)) it display 63.If we add any 1 or more value after 63 and convert it to VARCHAR(2) ie SELECT CAST(634 AS VARCHAR(2)) displays * by default .
Puzzal 2
SELECT (COUNT(*))
–O/P: 1
Thanks & Regards,
Abhijeet Desai
Bonas Q:
Ans:
NuoDB support for Windows, MacOS, Linux, Solaris, Joyent SmartOS Platforms.
Puzzle 1: SELECT (COUNT(*))
Puzzle 1: Result is truncated and then CAST displays * for varchar conversion.
Bonus Q: NUODB Supports 5 different types of OS
Hi Sir,
PUZZLE 1
——————
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
ANSWER : When we try to cast a numeric value to a varchar less than the length of the original numeric value
by default (*)star sign will be displayed in the result
Puzzle 2:
——————
Write the shortest code that produces results as 1 without using any numbers in the select statement.
ANSWER :
The shortest code that produces results as 1 is SELECT COUNT(*)
Thanks and Regards,
P.Anish Shenoy
Quadwave, Bangalore
Answer for bonus Q
NUODB supports Windows, MacOS, SmartOS, Solaris and Linux platforms
Thanks and Regards,
P.Anish Shenoy,
Answer for Bonus Q
Nuo DB Supports different OS :
1. MacOS 10.7 or higher
2. Windows
(32-bit, 64-bit)
3. RHEL 5 & 6 (64-bit)
4. SuSe 10 & 11 (64-bit)
5. Amazon Basic EC2 (64-bit)
6.Ubuntu 10 & 11 (64-bit)
7. Solaris 11 (Intel 64-bit)
8. Joyent SmartOS (Intel 64 bit)
Thanks and Regards,
P.Anish Shenoy
Quadwave, Bangalore.
1.You are casting three digit number with two digit varchar so it will give * ouput for any number greater two digits
2.select (len(‘sudhir’)/len(‘sudhir’)) it will give 1
Hi Pinal
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
Answer:
When we convert character or binary expressions like char,varchar,nchar,nvarchar 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. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.
In the above SELECT statement, the literal 634 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 3 bytes since there are 3 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 634 cannot fit in this size (varchar(2)) and the result will be asterisk(*).
Reference: https://www.microsoft.com/en-us/download/details.aspx?id=51958
https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Answer:
Some simple select statement to return 1
SELECT count(‘x’)
go
select len(‘x’)
go
select count_big(‘c’)
go
select @@Rowcount
go
select Rowcount_Big()
go
select Isnumeric(‘.’)
go
select Getansinull()
go
select Pi()/Pi()
go
select Isdate(getdate())
go
select @@Textsize/@@Textsize
go
select @@Datefirst/@@Datefirst
go
select @@Lock_Timeout/@@Lock_Timeout
go
select @@Max_Connections/@@Max_Connections
go
select @@Max_Precision/@@Max_Precision
go
select @@Options/@@Options
go
select @@Textsize/@@Textsize
go
select @@Rowcount/@@Rowcount
go
Thanks
Vijayakumar P
Kochi,India
3.select (len(‘S’))
Puzzle 1: The * indicates an overflow error caused by truncation
Puzzle 2: SELECT LEN(‘a’)
Bonus Q: 5 OS are supported – Windows, MacOS, Linux, Solaris, Joyent SmartOS
#1: An error is being produced because 634 is 3 characters but we are converting to varchar(2)
#2: Select CHARINDEX(‘a’,’abc’)
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
select len(‘a’)
Puzzle -2:
select EXP(”)