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
My third try: SELECT day($)
HI PInaldave,
Q1 – Varchar(2) is limited to 2 characters, while the response is 3 characters. Star is a common way to show when the result is longer than field length
Q2 – I have 2
Select COUNT(*)
Select LEN(‘a’)
NuoDb supports 5 different OS
Puzzle #1 When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded.
1 When CAST() fails to convert any expression of type tinyint, smallint or int to an either char or varchar datatype and produces incorrect resulting value that is too short to display, then it returns * . Here, the returned value * indicates that conversion happened with incorrect resulting value which is too short to display. This is the reason why the statement returns “*”:
2.select count(*)
1. It does not truncate the numbers when casting
2. SELECT ISDATE(GETDATE())
1. “Arithmetic overflow error converting numeric to data type varchar” not handled for numeric value as it handled for decimal values
2. SELECT ISDATE(GETDATE())
1. “Arithmetic overflow error converting numeric to data type varchar” not handled for numeric value as it handled for decimal values
2. SELECT len(‘.’) or SELECT ‘1’
Hi Pinal Dave,
Here is my contest entry…
Puzzle 1:
When executing “SELECT CAST(634 AS VARCHAR(2))”, an asterisk/star (*) is displayed because the varchar(2) is too short in length to display the 3-digit integer that is cast to it.
* = Result length too short to display (see table in “Truncating and Rounding Results” section of
Puzzle 2:
The shortest code I could think of that produces a result of 1 without using any numbers in the select statement is:
SELECT(LEN(‘A’))
OR
SELECT(COUNT(*))
Bonus Q:
How many different Operating System (OS) NuoDB support? 5: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Hi Pinal Dave,
Here is my contest entry…
Puzzle 1:
When executing “SELECT CAST(634 AS VARCHAR(2))”, an asterisk/star (*) is displayed because the varchar(2) is too short in length to display the 3-digit integer that is cast to it.
* = Result length too short to display (see table in “Truncating and Rounding Results” section of
Puzzle 2:
The shortest code I could think of that produces a result of 1 without using any numbers in the select statement is:
SELECT(LEN(‘A’))
OR
SELECT(COUNT(*))
Bonus Q:
How many different Operating System (OS) NuoDB support? 5: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle1: It is explained in the MSDN https://docs.microsoft.com/es-es/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 / Truncating and Rounding Results), the star means * = Result length too short to display.
Puzzle2: SELECT EXP($)
I took the puzzles and the solutions, and added a post to my Blog, for my Spanish readers:
1: * indicates that an error DID NOT occur and that the result length is too small to display as a result of the cast.
2: SELECT LEN(‘a’)
Bonus: Windows, MacOS, Linux, Solaris, Joyent SmartOS
1) VARCHAR(2) is too small, should be VARCHAR(3)
2) SELECT EXP(”)
Ans 1. This is a conversion from int to varcar. Since varchar length is smaller than the length of the integer, by default SQL server shows ‘*’. This is true for any conversion from int, smallint, tinyint to char or varchar of length less than the length of the int, smallint, tinyint.
Ans 2. SELECT(COUNT(*))
Hi Pinal,
Answer 1
When we convert 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. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following.
From data type: int , smallint, or tinyint
To data type: char and varchar
Result as * (star).
To data type nchar and nvarchar
Result as Error.
From data type: money , smallmoney, numeric, decimal, float, or real
To data type: char, varchar, nchar and nvarchar.
Result as Error.
364 as int so shows * as result.
Answer 2:
Select isnumeric(‘+’)
i use msdn for first answer
link: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
This happens because the size of varchar type is small then needs to be. So the ‘*’ (star) appears because when sql server did the cast the size of the varchar is not enough.So the sql server show a star(*).
For example:
SELECT CAST(123 AS VARCHAR(2)) dosen´t work.
SELECT CAST(123 AS VARCHAR(3)) It´s work!
Thank you!
Puzzel 2 Answer-
select COUNT(*)
This will produce Output as 1
SELECT EXP(”)
Answer for IInd Puzzle :
select ASCII(”)
Copy the script and paste it in ssms. When you run this script you will get 1 as result.
There is character, whose ascii value is 1, between quot’s.
——Script———
select ASCII(”)
——Script———
Puzzle 1
when converting number to varchar, if the varchar range is not properly set, any Arithmetic overflow error will display a star.
the same behaviour will be observed with select (convert(nvarchar(2), 634))
Puzzle 2
select (CHAR(49))
1. Length of value exceeds cast data type specification.
2. SELECT LEN(‘A’);
Select COUNT(GETDATE())