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 (
http://blog.SQLAuthority.com
)
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 http://msdn.microsoft.com/en-us/library/ms187928.aspx
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
Hi All,
Thank you for the great responses, all the valid answers will be kept hidden. Please make sure to to answer the Bonus Q. If you have missed it earlier you can still answer it in a follow up comment.
The contest is open till September 28th.
Kind Regards,
Pinal
Puz : 1 , the varchar is less than the number of characters in the select
Puz :2 , SELECT COUNT(*)
Bonus Q : 5
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
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer 1:
* Result length too short to display.
When converting 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.
E Error returned because result length is too short to display.
Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release
we can do roundtrip converion for: select cast(CAST(63 AS VARCHAR(2)) as int)
but not for : select cast(CAST(635 AS VARCHAR(2)) as int)
so sql server will show * because of its too short to display
–=================================================================================================
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
select (your answer)
Answer 2:
select ascii(”)
OR
select count(*)
–=================================================================================================
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer BonusQ:
MacOS
Windows
Linux (RHEL,Ubuntu,SuSe,Amazon Basic EC2)
Solaris
Joyent SmartOS
Puzzle 1: * Result length too short to display.
Puzzle 2: SELECT DATEPART(m,12-MONTH(GETDATE()))
Hi Pinal,
Answer for puzzle 1:
SELECT CAST(634 AS VARCHAR(2))
While you are casting a number value to varchar(2), so depending upon the
length of number, its not able to cover its complete length as you are casting it to varchar.
Lets take an example
select CAST(12341234 as varchar(1))
will give you * in result. This will give you * untill we are casting it upto varchar(7). Please note that length of number is 8. Now take another example:
select CAST(12341234 as varchar(8))
This will return the complete number – 12341234.
So in puzzle 1 – As per my example above
SELECT CAST(634 AS VARCHAR(2)) or SELECT CAST(634 AS VARCHAR(1))
will give * in result. While
SELECT CAST(634 AS VARCHAR(3)) will give complete number
In Puzzle1 data is truncated so ‘*’ is displaying. SQL guarantees that only roundtrip conversions are allows.
Answer Puzzle 1- When we Cast (int, smallint or tinyint) to an expression of a different data type (char, varchar) data can be truncated, only partially displayed because the result is too short to display. Here the length of 634 is 3 and target varchar type is 2 thats why partially result is displayed as ‘*’.
Answer Puzzle 2- select len(‘a’)
SELECT LEN(‘_’)
Just a note, we have crossed over 50 comments and many of them are very valid. Great going.
what about my answers Sir………
Hi
Puzzle 1 answer
This is because vachar size is 2 instead of 3.
Casting vice-versa must also work but that will not work in this case.
SQL here is not able to cast * to 634 which is violation casting rule.
Hence SQL is returning * .
Regards,
Ans.1 : It is happening due to varchar size. It should be increased as 3, to get the number as output “634″.
Ans.2 : Select count(*)
Ans.3 : NuoDB supports all Industry leading operating system.
Hello Pinal,
For display 1 without numbers. In general any short name function that return int, float ,etc will be divide by himself.
select -cos(Pi())
select Sign(Pi())
select Pi()/Pi()
Puzzle 1: Displayed result is an asterisk because converted number is too large to fit in excected data type. (*) in result means – “Result length too short to display”.
Puzzle 2: Shortest code I am able to come up with is:
SELECT COUNT(*)
Bonus Q: NuoDB supports 5 different families of operating systems: Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Answere for Puzzle 1:
634 is three digit Number casting by varchar(2) it will show * because Of Less Size of VARCHAR
i mean by casting 634 we use varchar(3) (because of three digit)
other wise it will show * in the result
the casting number(634) length must Less than or equal to varchar size
other wise it show * as result
Answere for Puzzle 2:
SELECT CAST(‘true’ as Bit)
Hello Pinal,
Better solution len(‘A’). Only 8 character
Puzzle 1:- Because the varchar(2) is not sufficient to accomodate the width of 3 digit no.
Puzzle 2:- Select len(‘a’)
Ans-1-it is show * becuase length of varchar data type is 2.
Ans-2-
SELECT ‘a’+CAST( COUNT(‘abc’) AS VARCHAR(1))
Answer for puzzle 2-
select (char(49))
This will return result 1 as expected.
Answer for puzzle 1:
SELECT CAST(634 AS VARCHAR(2))
While you are casting a number value to varchar(2), so depending upon the
length of number, its not able to cover its complete length as you are casting it to varchar.
Lets take an example
select CAST(12341234 as varchar(1))
will give you * in result. This will give you * untill we are casting it upto varchar(7). Please note that length of number is 8. Now take another example:
select CAST(12341234 as varchar(8))
This will return the complete number – 12341234.
So in puzzle 1 – As per my example above
SELECT CAST(634 AS VARCHAR(2)) or SELECT CAST(634 AS VARCHAR(1))
will give * in result. While
SELECT CAST(634 AS VARCHAR(3)) will give complete number
Answer for puzzle 2-
select (char(49))
This will return result 1 as expected
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer – Five OS
1. 634 is identified as an integer by SQL Server and Asterisk is the result when an integer is converted to a varchar, char, nvarchar or nchar and truncated.
2. SELECT len(‘a’)
3. 5 (Windows, MacOS, Linux, Solaris, Joyent SmartOS)
Puzzle 1:
Ans: If Result length is too short to display, then result would be *
Puzzle 2 :
Ans: SELECT (@@ROWCOUNT)
Bonus Q
Ans: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1:
Answer:-Simply as we all known Cast() function is used for data conversion.When the cast() fails or whlie the conversion data can be truncated,partially displayed or an error is returned because the result is too short to display.When we convert “int/tinyint/bigint” to “char” or “varchar” and in that case if the cast() fails then it returns “*”.Here the “*” indicates that the conversion happened with an incorret value which is too short to display.
SELECT CAST(634 AS VARCHAR(2))
In above SELECT statment, for the value 634 to be converted successfully to varchar type variable , 634 needs 3 bytes as 1 byte for each digit to be stored as character. But in above statement the target expression is of type varchar(2) which can store only two bytes. So the value 634 can not fit into box of varchar(2) and results in incorrect resulting value i.e. “*”.
In above SELECT statement, any expression which requires more than 2 characters to represent the value will not be converted successfully to varchar(2) and returns “*”.
In other words, only 1 or 2 digit values or values between 0 to 99 can be converted and if you want to convert 634 you have to use varchar(3) or greater or you can simply use “varchar” instead of “varchar(max)” means their is no requirement of providing length with target expression i.e. “varchar”.
e.g-SELECT CAST(634 as VARCHAR)
Puzzle 2:
Answer:-SELECT COUNT(*)
Bonus Q:
Answer:-Windows, MacOS, Linux, Solaris
Puzzle 1 – Answer – Result length too short to display
Puzzle 2 – Answer – select ASCII(”)
Puzzle 1- Answer – Result length too short to display
Puzzle 2- Answer – select ASCII(”)
select ISDATE(getdate())
Puzzle 1- Answer – Result length too short to display
Puzzle 2- Answer – select ISDATE(getdate())
select ASCII(”)
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer:
* (star) because the Result length too short to display.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Answer:
select COUNT(*)
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer:
5(They are Windows, MacOS, Linux, Solaris, Joyent SmartOS)
1. The size of the integer 634 is not matching with destination data type. Source has length 3 and destination is 2. So it is giving answer as *.
SELECTCAST(634 AS VARCHAR(1)) also return *
2. SELECT COUNT(*)
Bonus Qustn
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1:
Per Books On Line, “Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.” The table then goes on to show that int, smallint or tinyint values, if becoming too short to display manifest as “*”.
Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Puzzle 2:
SELECT COUNT(*)
Bonus Q:
MacOS
Windows
RHEL,SuSe,Amazon Basic EC2
Ubuntu
Solaris
Joyent SmartOS
p1:If Target size is less than source size then it will show *
p2: select LEN(‘a’)
Hello Pinal, here’s my answers:
Puzzle 1: it returns * because the number is 3 digits long and the sql statement is trying to cast it as a varchar long 2.
Puzzle 2: select RIGHT(ASCII(”)),1)
Bonus Q: 5 OS
Have a nice day!
Puzle 1. Due to Truncation when converting int to varchar. This will throw error when converting to nchar, nvarchar
Puzzle 2. SELECT (SELECT 1))
Puzzle 2:
SELECT COUNT(‘A’)
the answer are as belows
Ans 1) When you 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.
* = Result length too short to display
Ans2) select (count(*))
Answer for the first question :
You have to specify the correct length
SELECT CAST(634 AS VARCHAR(2))
It should have been VARCHAR(3)
So the query when rewritten : SELECT CAST(634 AS VARCHAR(3))
Answer for the second question :
Asked shortest query :
SELECT CAST(‘*’ as CHAR)
Bonus Question answer :
Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS
1)Answer
When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated
* = Result length too short to display. E = Error returned because result length is too short to display
2) Answer
select (count(*))
1. SELECT CAST(634 AS VARCHAR(2)) results in ‘*’ because Int is implicitly convertible to varchar and if the length to convert is less than the no of digits then it results as a warning .
* = Result length too short to display
2. SELECT (@@ROWCOUNT)
SELECT CAST(‘true’ AS bit)
SELECT CAST(‘true’ AS bit)
1. SELECT CAST(634 AS VARCHAR(2)) results in ‘*’ because Int is implicitly convertible to varchar and if the length to convert is less than the no of digits then it results as a warning .
* = Result length too short to display
2. SELECT (@@ROWCOUNT)
BonusQ:
OS Supported by NuoDB:
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)
Select CAST(‘True’ AS Bit)
PUZZLES -1:
As we all have seen that it is very common to convert values from one data type to another while working with data. Most of the time these conversions are achieved using implicit conversion approach. There are two ways of converting values of one data type to another data type and they are referred to as Explicit Conversion and Implicit Conversion. Implicit conversion is achieved by using a value as it is already a desired type of data. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. In order to convert a value from one data type to another data type explicit conversion should be used using CAST or CONVERT functions.
When we convert character or binary expressions like 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. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.
First Case:
view sourceprint?1.SELECT CAST (634 AS VARCHAR(2))
In the above SELECT statement, the literal 2004 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 4 bytes since there are 4 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 2004 cannot fit in this size (varchar(2)) and the result will be asterisk(*).
Second Case:
view sourceprint?1.SELECT CAST (’634′ AS VARCHAR(2))
In the above SELECT statement, as per the BOL, the conversion to varchar will be truncated to get the result of the length specified in target type. Generally, CAST function converts any expression of type char, nchar, nvarchar, varchar, binary, or varbinary to an expression of any type can convert without any errors. But in the above example, CAST function truncates to the specified length of 2 used in the target type length and it does not fit into the length specified, therefore, CAST function truncates 2004 last two digit (04) and only first two digits (20) are returned. Therefore, the second select statement returns the value of 20 using CAST statement.
PUZZLES – 2:
select count(*)
select len(‘a’)
Thanks,
Kaushik Patel
PUZZLES -1:
As we all have seen that it is very common to convert values from one data type to another while working with data. Most of the time these conversions are achieved using implicit conversion approach. There are two ways of converting values of one data type to another data type and they are referred to as Explicit Conversion and Implicit Conversion. Implicit conversion is achieved by using a value as it is already a desired type of data. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. In order to convert a value from one data type to another data type explicit conversion should be used using CAST or CONVERT functions.
When we convert character or binary expressions like 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. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.
First Case:
view sourceprint?1.SELECT CAST (634 AS VARCHAR(2))
In the above SELECT statement, the literal 2004 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 4 bytes since there are 4 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 2004 cannot fit in this size (varchar(2)) and the result will be asterisk(*).
Second Case:
view sourceprint?1.SELECT CAST (’634′ AS VARCHAR(2))
In the above SELECT statement, as per the BOL, the conversion to varchar will be truncated to get the result of the length specified in target type. Generally, CAST function converts any expression of type char, nchar, nvarchar, varchar, binary, or varbinary to an expression of any type can convert without any errors. But in the above example, CAST function truncates to the specified length of 2 used in the target type length and it does not fit into the length specified, therefore, CAST function truncates 2004 last two digit (04) and only first two digits (20) are returned. Therefore, the second select statement returns the value of 20 using CAST statement.
PUZZLES – 2:
select len(9)
select count(*)
select len(‘a’)
Thanks,
Kaushik Patel
Hi guys,
P1: This is because the result is being truncated and the result is too short to display.
P2: SELECT CAST(‘true’ AS bit)
Thanks.
1) select count(‘This is The Answer of First Question’)
2) explicit casting result length too short to display for 634 in just 2 char
answer for bonus que is
nuoDB supports following os Windows, MacOS, Linux, Solaris, Joyent SmartOS
Q1:SELECT CAST(634 AS VARCHAR(2))
If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to an int), SQL Server generates an error message.
When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default
When converting 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.
http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx
Q2:
Ans: select COUNT(*)
Q3:How many different Operating System (OS) NuoDB support?
Five: Windows, MacOS, Linux, Solaris,Joyent SmartOS
Partha
India
Puzzle 2:
SELECT Count(‘A’) ‘Data’
Hi,
This is my answer. Question 1: When converting number to character type, the result display * if result length too short to display.
Question 2: select cos($)
1. Because conversion from 634 does not fit to varchar(2). The culprit is the size 2.
2. Select Count(‘x’)
Bonus : NuoDB support 5 different OS. Windows, MacOS, Linux, Solaris, Joyent SmartOS.
1 ) SELECT CAST(634 AS VARCHAR(2))
ANSWER
the result length is more than 2 characters that’s why it display *… this rule will apply to all casts where result then is greater then specified length
like SELECT CAST(10 AS VARCHAR(1))
2) ANSWER
select @@ROWCOUNT
1.In sql server when you try to convert int data types to char or varchar and result is too short to display then it displays ‘ * ‘.
2. Select count(‘A’) will return 1 as output
Bonus question answer is NuoDb Supports 5 OS (Windows, MacOS, Linux, Solaris, Joyent SmartOS)
Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.
Puzzle 2: SELECT COUNT(*)
Bonus Puzzle: 5 different OS’s
Puzzle 1 : When casting data size is greater than the allocated varchar data type, the result will be shown as star. This also indicates the allocated size is not sufficient for casting
Puzzle 2 : SELECT LEN(‘a’)
Hi!
The answer to the first question is – because this is how conversion in sql server works. If the length of converted string exceeds the desired string length, then converted string simply replaced with *.
Internally the magic happens in function sqlCXVariant::StrConvertFromI4. According to WinDbg here is what happens:
1) the abs value is taken from the converting int value;
2) loop begins
3) inside loop on each iteration argument is divided by 10 and %10 to get one digit symbol, after that number 48 is simply added to get ascii code;
4) loop ends
5) check if the argument was negative minus symbol is also added
6) perform length check of converted with desired length if it is larger then simply replace string with char 42 and call the next function CXVariantBase::StrAdjustPadding
here is how it looks like:
–this is performed inside cycle, counting number of converted bytes
sqlservr!CXVariant::StrConvertFromI4+0x4d: add edi,1
–here is what we have in edi after cycle: edi=00000003
…omitted for brevity…
–copy desired length from memory to eax
sqlservr!CXVariant::StrConvertFromI4+0×65: mov eax,dword ptr [ebx+8] ds:0023:337fcc70=00000002
–here is what we have in eax:eax=00000002
–compare results
sqlservr!CXVariant::StrConvertFromI4+0×68: cmp edi,eax
–perform jump to the specific brunch of code of the same function StrConvertFromI4+0x6c
sqlservr!CXVariant::StrConvertFromI4+0x6a: ja sqlservr!CXVariant::StrConvertFromI4+0x6c (0160da7b) [br=1]
–next instuction starts at this adress+0x6c
–sqlservr!CXVariant::StrConvertFromI4+0x6c: test eax,eax
…omitted for brevity…
sqlservr!CXVariant::StrConvertFromI4+0×76: mov byte ptr [edx],2Ah ds:0023:344e24d0=28
…further steps….
7) if not, perform some further manipulations by calling function CXVariant::LStrConvertToStr.
Here is my variants of the second one puzzle:
select ascii(”)–i’m not sure if this one will work after copy/paste to/from web, in case it won’t the idea is to put here char(1) symbol, and it really gives desired result
select count(*)
when Result is too short to display so sql give the star(*) sign as result.here in this question casting of int to varchar(2) so its give the result as *.
* is displayed because we are casting 634 int to varchar(2) that is result is too short to display….so it is giving * because varchar(2) is too small and we are converting int to varchar…..thank you
We have officially received 100 entries so far! Love the communication and few indepth answers.
Dear Pinal,
The answer for today puzzle are
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans : Result length too short to display.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Ans : SELECT COUNT(*)
Regards,
Prasanna kumar.D
Bonus Q:
How many different Operating System (OS) NuoDB support?
Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1 –
SELECT CAST(634 AS VARCHAR(2)) –
This statement should convert 634 into VARCHAR(2) datatype. So the expected result is “634″. But the target datatype’s size is 2, which is less than the size of the actual result (which is 3 here). So the result needs to be truncated. The * here just represents the truncation of the resultant data.
Puzzle 2 –
Displaying 1 without using a number –
SELECT count(”)
Bonus Q –
NuoDB is an amazing product with awesome concept of cloud database. It has support for all major OSes like – Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Hi Pinal,
For Question no. 2 (Write the shortest code that produces results as 1 without using any numbers in the select statement.)
The query is
select ASCII(‘b’)-ASCII(‘a’);
Thanks
Manish
1) Answer for first question is
Result will be * in all the cases when the length of the integer is more than the casting length defined for char/varchar
2) Answer for second question is
SELECT CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)/CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)
Puzzle 1: The result displays a * as the int conversion is truncated.
Puzzle 2: SELECT CAST(‘true’ AS bit)
Bonuz Q: NuoDB supports Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Answer to bonus question is 5. They are Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1: The conversion will lead to loss of data so the output is shown as *. As explained by MS “It requires explicit CAST to prevent the loss of precession or scale that might occur in an implicit conversion”
Puzzle 2: Select FLOOR(TAN(45)) — 1
Let me know if I am correct.
Hi Pinal,
1) * = Result length too short to display
2) SELECT LEN(‘a’)
1. Since the varchar size is 2 but the number 634 size is bigger than the varchar size, the output results as a star(*)
2.select len(‘a’)
3. 6
Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.
Puzzle 2: SELECT EXP(”)
Bonus Puzzle: 5 different OS’s
Puzle 1. Due to Truncation when converting int to varchar, SQL Server defaults the value to ‘*’. This will throw error when converting to nchar, nvarchar
Puzzle 2. select (select CHARINDEX(‘A’,'A’))
in the puzzzle 1 we are casting integer to varchar but as we all know char dont have sufficient bytes of space to store integer 634.
So it is displaying star (*) because of casting to varchar from interger.
Q1 : * is the error result, 634 is long to display in varchar(2)
Q2 : select CAST(‘true’ as bit)
Bonus Q: : MacOS, Windows, RHEL, SuSe, Ubuntu, Solaris , Joyent SmartOS
select cast(634 as varchar(2))
Result shows ‘*’ because of
* means it requires implecit CAST to prevent the loss of precision.
So that Query should like
SELECT CAST(634 AS VARCHAR(3)) will shows the right out put
OR
SELECT CAST(634 AS VARCHAR) In this Precision set Itself as requires.
star comes for numerical values and for Decimal value if precision is small then it shows “*”
Thank you & Regards
Kaushal Dhora
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
SELECT count(‘ ‘)
Puzzle-1 Ans :if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.
Puzzle 1:
Result length too short to display
Puzzle 2:
SELECT COUNT(*)
if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result
Ans 1: number are you trying to cast has length more than the size it try to cast.
Number length is 3 and trying to convert in size 2, so we are getting result as *.
Puzzle 1 : if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.
Puzzle 2 : SELECT COUNT(*)
shortest code for result 1 is SELECT EXP(‘ ‘)
Puzzle 1 : if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.
Puzzle 2 : SELECT COUNT(*)
select LEN(‘a’)
or
select count(*)
or
select exp(”)
Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.
Puzzle 2: SELECT EXP($)
Bonus Puzzle: 5 different OS’s
ANSWERS :
PUZZLE 1:
FROM DATATYPE : int , smallint, or tinyint
TO DATATYPE : char , varchar
RESULT : *
FROM DATATYPE : int , smallint, tinyint , money , smallmoney, numeric, decimal, float, or real
TO DATATYPE : char , varchar , nchar , nvarchar
RESULT : E
* = Result length too short to display. E = Error returned because result length is too short to display.
PUZZLE 2:
select exp(”)
in the puzzle 1 we are casting 3 digits and we have written size of the varchar is 2 so because of that it is displaying *.
It is the property of char and varchar that when we try to cast more digits or char den we written it will display *.
and for 2nd puzzle the the shortest code foe displaying 1 is
Select EXP(”)
Answer 1
SELECT CAST(634 AS VARCHAR(2))
we have taken length as 2 but 634 is having length 3 hence.It is showing *
Answer 2
SELECT CAST(64 AS VARCHAR(1))
1. Overflow
2. select LEN(‘a’)
PUZZLE 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Answer:
select LEN(‘u’)
SELECT (ASCII(‘B’) -ASCII(‘A’))
When integers are implicitly 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 (*).
PUZZLES – 2:
select Exp(”)
select count(*)
select len(‘a’)
select len(‘a’)
Answer 1) Due to overflow from trying to cast a 3 digit integer down to a varchar of size 2.
Answer 2) SELECT COUNT(‘A’)
puzzle 1: while converting int to varchar and given length is less then the given number in this case result is very small for display and it will show *
puzzle 2: sortest method for getting 1 without usin number
select count(*)
Puzzle 2: select COUNT(*)
Puzzle 1: resulting expression too small to display then cast values that why we are getting ‘*’
Puzzle 1: As the varchar is restricted to 2 places, it is showing * for 3 digited 634.
Puzzle 2: select DB_ID(‘master’)
Bonus Q: 5 Operating System
The value 634 does not fit in 2 characters (CHAR(2) or VARCHAR(2)). You need at least 3 chars.
Oh! and for the second question:
select count(getdate()) –> 1
Q1: When integers are implicitly 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 (*).
Q2: NuoDB supports Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Here is the answer for the first puzzle:
The select statment is trying to convert a number with three algarisms which is bigger than the maximum number fo algarisms allowed by varchar(2), namely, 2 characters.
Here is the naswer to the second puzzle:
Select ASCii(‘A’)/ASCii(‘A’)
Puzzle 2:
ANS : SELECT (LEN(‘a’))
Puzzle 1 : According to SQL Help on the CAST function : “When you 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 table…”
So, conversions from int, smallint or tinyiny to char, varchar, nchar, nvarchar, binary, and varbinary are not truncated. That’s why in the SELECT CAST(634 AS VARCHAR(2)) statement, the “*” is displayed, because the result field is too short to display the value.
Puzzle 2 : SELECT ISDATE(GETDATE())
Bonus question : NuoDB supports 5 operating systems
Hello. My answers :
1- The result length is too short to display. Converting data type int to varchar does not imply trucation when the result length is shorter than necessary.
I we had typed “SELECT CAST(634 AS VARCHAR(3))” the result would have been ’634′
2- SELECT COUNT(*)
That’s the shortest I could find.
Bonus Q – 5 different OSs (Windows, MacOS, Linux, Solaris, Joyent SmartOS)
Bonus question:
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle2:
select COUNT(‘a’)
Puzzle1:
since we mentioned it as varchar(2) but we are referring to a 3digit number, it is unable to print the number. it will work in same manner if we reffer to any number more than 2 digits.
1) in select statement if we use varchar (3) it will display the 634 otherwise its is dispalying star
2) select len(‘A’)
Answer1: SQL can’t convert the 3 digit number to a varchar(2), so it replaces the value with a star because it doesn’t fit. A varchar(3) would be needed.
Answer2: SELECT COUNT(”)
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans 1:
634 is numeric, it cant be convert/reduced to varchar with the size of 2 thats why it returns “*”
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
SELECT CAST(REVERSE(ASCII(‘d’))AS INT) — will returns 1
Bonus Q:
How many different Operating System (OS) NuoDB support?
Nuo DB will support 5 OSs which are Windows, MacOS, Linux, Solaris, Joyent SmartOS.
notes:
I could not able to answer all questions early morning at 7 : 10 am, coz the site http://www.wordpress.com was blocked at my office.
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans 1:
A 3 digit numeric value cannot be converted/reduced to varchar with size of 2
this is the reason the result returns “*”
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
ANS 2:
SELECT CAST(REVERSE(ASCII(‘d’))AS INT)
– the result will returns int-1
Bonus Q:
How many different Operating System (OS) NuoDB support?
The NuoDB will support 5 OS which are Windows, MacOS, Linux, Solaris, Joyent SmartOS
Note:
My office firewall blocked http://www.wordpress.com‘s site thats why i couldn’t answer these questions early morning 7:10 am.
thank you. this kind of puzzles are interesting and innovative.
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
Answer from http://msdn.microsoft.com/en-us/library/ms187928.aspx
* = Result length too short to display.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Answer SELECT (@@rowcount)
Bonus Q:
How many different Operating System (OS) NuoDB support?
5, Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS
select (cast(‘TRUE’ as bit))
1) The varchar(2) doesn’t have enough room to show the number so it resorts to the *. If you use varchar(3) or greater then it displays correctly.
2) select ASCII(”)
Bonus: 5 operating systems
SELECT ~CAST(\ AS BIT)
Puzzle 1:
634 converted to a char. string is ’634′, too large for a varchar(2) (much less a varchar(1)); if the 2 in the puzzle is changed to a 3, i.e.
SELECT CAST(634 AS VARCHAR(3))
a result other than “*” will be displayed. “*” is displayed because the result is too large — it would likewise be displayed if we attempted to CAST any three- (or larger) digit number, as opposed to 634, into a VARCHAR(2).
Puzzle 2:
Pick any table with at least one “int” column IC containing data and SELECT MAX(IC/IC) it. As an example, I connected to a server with a msdb database that has a non-empty table sysjobs and executed this statement:
SELECT MAX(start_step_id/start_step_id) FROM sysjobs
Bonus Q:
According to the Webpage you “hint”ed at, nuoDB has “[s]upport for Windows, MacOS, Linux, Solaris, Joyent SmartOS”, i.e. five different operating systems.
Hello Pinal,
Again, great puzzles, thanks!
Puzzle 1:
The result is a star, because varchar(2) does not allow to display the entire string. Use instead: SELECT CAST(634 AS VARCHAR)
Puzzle 2:
select ascii(‘b’) – ascii(‘a’)
or shorter:
select ascii(”) — the character you get by: select char(1)
Both probably not what you expect?! :-)
Bonus Q:
5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Best regards,
Sparks
SELECT LEN(‘a’)
Puzzle #1
collate & implicite cast
>> * = Result length too short to display
Pinal, I have taken from your earlier post of how to display 0. Here is the simple answer to display 1
select count(*) where ‘a’!=’b’
1) Conversions from int to varchar are truncated when result length is too short to display.
2) select len(‘A’)
Bonus) NuoDB supports 5 OS
1).
Overflow, you are trying to convert the number 634 to a string but only giving the string 2 characters of space where 3 are needed.
2).select LEN(‘a’)
As comments with answers are kept hidden till 28th – you are welcome to discuss with me on twitter http://twitter.com/pinaldave and on facebook at http://facebook.com/SQLAuth – let us discuss :-)
Puzzle 1:
The reason is the result is too short to display.
The result will be same for char, varchar, nchar, nvarchar. The correct result will be obtained for below query:
SELECT CAST(634 AS CHAR(3))
Puzzle 2:
SELECT Count(‘Your Answer Here’)
Bonus question:
The supported OS are Windows, MacOS, Linux, Solaris, Joyent SmartOS.
1. Becase cannot convert 3 digit number to varchar(2)
Same will hapen if we try SELECT CAST(63432 AS VARCHAR(3))
2. SELECT COUNT(*)
PS: Do not want any reward, just playing, but please reply me with the correct answers or write some blog post.
1 – Cast overflow error converting INT to VARCHAR returns *
2 – SELECT CAST(PI() AS BIT)
3 – 5
SELECT(CHAR(49))
1. The result is truncated because varchar(2) is too short to display the result correctly. Ref: ‘Truncating and Rounding Results’ in ‘CAST and CONVERT’ in Books Online.
2. select ascii(‘b’) % ascii(‘a’);
Bonus: 5
1) SELECT CAST(634 AS VARCHAR(2))
– If length of source is larger then specified size at the time of conversion then the * is an indicator of overflow.
2) SELECT (CHAR(49))
Ans 1 Cast is fucntion by which we can convet the values.
Ans 2 Select Count(*)
Ans 1.
SELECT CAST (634 as varchar(2)) : Interprets conversion of expression from int data type to varchar
While Casting, When the input of CAST is not a character string, the output has the default collation of the database, and a collation label of coercible-default. In such case, when the input is int, smallint, or tinyint and the output of CAST is varchar, the result is *
Ans 2.
Select Len(‘a’)
Test
Q1 answers is : select COUNT(*)
Q2 answers is : because varchar(2) size is less than no of digits in the following no. 634 (no. of digites are 3)
Q3 answers is : 5 os
SELECT CAST(634 AS VARCHAR(2))
returns * because of not enough characters in VARCHAR, VARCHAR(3) would return the value 634.
SELECT (ASCII(‘B’)-ASCII(‘A’)); would return a value of 1.
Name: Ramdas
Location: Charlotte,NC,USA
Q1)
Ans. It is showing * star because of error.
If we replace 634 with any digit number from 1-99 it will show the number except * star.
But if we replace 100 den it will show * as an error but if we increase the varchar size to 3 nd den write 100 or any value till 999 it sill show the number itself but again if we write 1000 as varchar(3) it will show * as an error
1) The integer is too large to fit into the character length, so SQL returns an ASCII 42 (*)
2) SELECT CAST(‘true’ AS BIT)
Puzzle 1:
Because SQL Server returns ascii character 42 (aka ‘*’) when it implicitly tries to convert an integer that is too large for it’s character field.
Puzzle 2:
select len(‘a’)
Puzzle 1: String truncation
Puzzle 2: SELECT Length(‘L’)
Bonus Q: 5 Operating Systems
Puzzle1: When you cast 3 characters lke 634 then you need to give in Varhcar(3) not Varchar(2)…….It’s like an overflow character.
Puzzle2: select (count(*))
Puzzle 1: when converting INT, SMALLINT or TINYINT to CHAR, or VARCHAR using CAST, and the number don’t fit in the CHAR, like trying to fit 634 in 2 characters, the result is just the character *.
Puzzle 2: select SIGN(PI())
Bonus Q: NuoDB supports 5 OSs
1.
* means the result length is too short to display
see http://msdn.microsoft.com/en-us/library/aa226054(v=SQL.80).aspx
2.
select (’1′)
– ’1′ is not a number, but a string
Select count(”)
Hello! An interesting quiz!!
A1) The * is displayed because the resulting length is too short to display the casted value.
A2) SELECT COUNT(*)
Puzzle 1. Length should equal to 3 or more.
Select count(*) will produce a 1
select count(*)
Not sure if you got it, since I had wordpress moving me around window to window
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: http://msdn.microsoft.com/en-us/library/ms187928.aspx)
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())))
Thank you guys we have officially received 200+ entries.
For First puzzle:
SELECT CAST(634 AS VARCHAR(2))
length of 634 is greater than 2 so it cannot accomodate in varchar of 2. if you give varchar(3) it ll display 634.
For Second Puzzle:
select (ASCII(‘B’)-ascii(‘A’))
Please let me know if my answers are correct.
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
Hi Pinal,
Second answer for Puzzle no. 2 is as follows.
select CHAR(49)
Thanks
Manish
Puzzle 1 Answer:
SELECT CAST(634 AS VARCHAR(2))
When we convert int, smallint or tinyint to Char or varchar, if result length is too short to display then result is shown as *
ref: http://technet.microsoft.com/en-us/library/ms187928(v=sql.100).aspx
————————————————————————————————
Puzzle 2 Answer:
select LEN(‘a’)
Question 3: How many different Operating System (OS) NuoDB support?
Answer: Windows, MacOS, Linux, Solaris, Joyent SmartOS
The results for Puzzle 2:
select (ascii(‘b’)- ascii(‘a’))
Puzzle 1:
When an int, tinyint or smallint is converted to a varchar (or char) and is truncated a * is returned.
Puzzle 2:
SELECT CAST(‘true’ as BIT)
Bonus Q:
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Good fun!
Graham
Ohh I am late to this contest but would like to post the answer.
Answers:
Puzzle01: SQL Server throws a * because we are passing 3 string characters with assigning 2 in memory. Technically it’s an overflow. If we will assign VARCHAR(3) then we will get 634.
Puzzle02: SELECT LEN(‘A’)
There are many way to solve the puzzle 2 but I feel above is the shortest SELECT query to get the result 1
because of Over flow it showing * if we place the 3 digits then the var char (3) is ok else over flow comes
Because of Overflow.
SELECT LEN(‘A’)
SELECT CAST(532 AS VARCHAR(2)) — This is because of the overflow of 532 which is of 3 characters
select LEN(‘L’) — This gives the result as 1.
ANswer 1: because of Overflow. there are three integers in the given number and we are casting it in the length of two character string. so, result is ‘*’ and it indicates that result length too short to display.
Answer 2: Select Count(0)
Bonus Q: 5 different Operating System (OS) supports NuoDB
1. That is because of the digit overflow. 3 digits can’t fit into varchar(2). Same is the case with SELECT CAST(61 AS VARCHAR(1))
2. select (count(*))
second question’s answer is SELECT (ASCII(”))
same result for the following query
SELECT CAST(634 AS VARCHAR(1))
select len(‘a’)
puzzle 1
Ans:
SELECT CAST(634 AS VARCHAR(2))
Because of data truncation sql server produces * data truncation.
whenever data truncation for following explicit conversion sql server results *.
int, smallint, or tinyint -> char,varchar
puzzle 2
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Ans:
SELECt CHAR(ASCII(‘Q’)-ASCII(”))
Bonas Q:
How many different Operating System (OS) NuoDB support?
Ans:
5
1) SELECT CAST(634 AS VARCHAR(2)) – Returns a star because of loss of precision
2) SELECT (CAST(‘true’ as bit))
Bonus: 5 unless you count the flavors of Linux as seperate
when increase size of varchar it return real data, 634 or any 3 digit no. is not coming in range of varchar size which is given in varchar(2). conversion from int and smallint to varchar which size is smaller than given int value length, is not truncated. that’s why it returns *.
and second answer is .
SELECT (CAST( ‘true’ AS bit))
Puzzle #1:
(help in SSMS)
When you 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 table.
Data type => to data type => Result
int, smallint or tinyint => VarChar => *
Puzzle #2:
select COUNT(*)
Bonus:
Answer: 5
1. Windows
2. MacOS
3. Linux
4. Solaris
5. Joyent SmartOS
Puzzle1: * is displayed because result length too short to display. String ’634′ of 3 chars doesnt fit in varchar(2)
Puzzle2: SELECT LEN(‘x’)
Bonus Q: 10 Operating systems. Note: RHEL 5 & 6 is considered as a single operating system here.
#2. SELECT ASCII(”); The SOH character is created by opening up a text editor, entering ALT-001 and copying and pasting the result into the SQL statement.
My original comment didn’t format correctly. The command is: SELECT ACII(‘SOH’); The SOH character is x01, which is created by opening up a text editor, entering ALT-001 and copying and pasting the result into the SQL statement.
Because it tries to convert 634 to character form but since the size of varchar declared is only 2, it returns unkown data
select count(*)
select ascii(”) for the second quiz
1. When you 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. (per books online) An * = Result length too short to display.
2. select day(”)
In short, a * means the length of the result is too short to display. * is the result of an int, smallint, or tinyint (from) datatype whose length is longer than the length defined for the char or varchar (to) datatype.
“E” would be the result if the (to) datatype was nchar or nvarchar in this scenario. “E” would also be the result if the (from) datatype was money, smallmoney, numeric, decimal, float or real and the (to) datatype was char, varchar, nchar, or nvarchar in the same scenario.
select isnumeric(‘+’) returns 1
Ans 1) varchar(2) is too short to display conversion result , so it is displaying * (Star).
Ans 2) I have found 7-8 ways to get result 1 without using any number in select statement. But, in terms of shortest code, I have found 2 statement with exactly same size.
First answer is inspired by one of your earlier post
Select Count(*)
And Second answer is
Select len(‘A’)
However, 2nd answer is shortest in terms of execution plan also.
Ans of Bonus Question ) NuoDB supports different Operation systems like Windows , MacOS, Linux, Solaris & Joyent SmartDB.
SELECT LEN(‘A’)
SELECT COUNT(*)
SELECT @@ROWCOUNT
SELECT PI()/PI()
SELECT POWER(234234234, 0)
SELECT ASCII(‘B’) – ASCII(‘A’)
SELECT MONTH(’1 JAN 1990′)
Answer for Puzzle 1:-
SELECT CAST(634 AS VARCHAR(2))
* = Result length too short to display (special case of Truncating and Rounding Results) , SQL used to display * for this case. SQL gives Result as E when you convert money to varchar.
E = Error returned because result length is too short to display.
Answer for Puzzle 2:-
SELECT LEN(‘a’)
Answer for Bonus Question:-
I am not pretty sure for this answer, I believe NuoDB support SmartOS.
Regards,
Girijesh
Puzzle1 : Because the Varchar length(2) is lesser than number of digits(3).
Puzzle 2:
SELECT ASCII(‘B’)-ASCII(‘A’)
select count(*) from dual;
1. length of 634 is 3 so when we convert it into varchar(2) it print *
2. select count (‘abc’)
3. 5 OS supported
Puzzle 2 Solutions : Select COUNT(*)
1.because we are converting 3 digit number to 2 digit varchar,which is an implicit conversion.so due to length of varchar,data will be lost.So in case of lost data,sql server displays * everytime
2.select len(‘m’)
Puzzel-1 :
* = Result length too short to display
Puzzel -2 ans :
select count(*)
Hi Lakshmi Here,
Answer for Puzzle 1: It gives star because when the output length exceeds provided,.A three-character result cannot fit into a two character space.
Answer for Puzzle 2: select len(‘a’)
Puzzle 1 – Answer
The space provided is for 2 character, but since there are 3 characters SQL server uses shorthand and displays *
Puzzle 2 – Answer
SELECT CAST(GETDATE() AS INT)/CAST(GETDATE() AS INT) AS ANSWER
Puzzle 1) Converting tinyint, smallint or int to char or varchar(in the case of the puzzle) will display asterisk because the result is too short to be displayed.
Puzzle 2) SELECT ISDATE(GETDATE());
Sorry missed the bonus question.
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Answer on first puzzle in on the following link
http://msdn.microsoft.com/en-us/library/ms187928%28SQL.90%29.aspx
in “Truncating and Rounding Results” section.
Answer on the second puzzle is
SELECT ASCII(‘b’) – ASCII(‘a’)
Answer for the first puzzle is here:
http://msdn.microsoft.com/en-us/library/ms187928%28SQL.90%29.aspx
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: http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx
http://msdn.microsoft.com/en-us/library/ms176089.aspx
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(”)
Puzzle 1:
* means Result length too short to display
Puzzle 2:
select exp(”)
Question 1: the result is too small for the size of the number therefore the cast fails and a ‘*’ is displayed as the result.
Question 2: declare @x bit=0; select ~@x
Hi Pinal:
Puzzle 1: The star (*) is displayed because the results is too large to fit – this is similar to Excel displaying ‘###’ in columns that are not wide enough to display the data.
Puzzle 2: select COUNT(*) is the shortest select to produce result of 1 without using numbers If needed, it can be select (COUNT(*)).
BONUS Q: 5
Thanks and have a great day,
Dave
Here is the query.
select len(‘a’)
Puzzle 1:
SELECT CAST(634 AS VARCHAR(2))
it returns * because the conversion result set is too low to return the entire result.
If we change the query to
SELECT CAST(634 AS VARCHAR(3))
then it will returns 634.
select (*)
Question 1: The type is to short for the conversion so Sql returns an asterisk.
Question 2:Select CAST(‘True’ as Bit)
1. Because converting 634 to varchar(2) would truncate string, then (by design) CAST returns ‘*’. however when try to cast it to varchar(3) – it would return 634
Next MSDN link shows that:
http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx
2. I think the shortest code that returns 1 would be
Select Len(‘a’)
Puzzle 1:
From BOL: http://msdn.microsoft.com/en-us/library/ms187928.aspx under Truncating and Rounding Results for VARCHAR: * = Result length too short to display.
Puzzle 2:
SELECT LEN(‘.’)
A1. Result length too short to display.
A2. SELECT db_id(‘master’)
BA. Windows, MacOS, SmartOS, Solaris and Linux platforms.
Arithmetic overflow.
puzzle 2: SELECT LEN(‘A’);
select ASCII(‘A’)/ASCII(‘A’)
select count(*)
Puzzle1 Response:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Refer to “Truncating and Rounding Results”
Puzzle 2:
select CAST(ascii(‘o’) AS BIT)
Puzzle 1:
* is define in cast function and it appear if the result length too short to display.
in SELECT CAST(634 AS VARCHAR(2)) we are trying to cast a number of three length into two length of varchar,
SELECT CAST(’634′ AS VARCHAR(2)) if we define it like this it will returns 63 because datatype is compatible and no need of explicit conversion.
Puzzle 2:
Select Len(‘A’)
Q1: because of Overflow, as you are trying to cast the 3 digit number on 2 letter varchar. If you use Varchar(3), you will get the 634 as answer.
Q2: SELECT LEN(‘A’)
1. As the length of the integer(682) is greater than specified in the Cast Function (i.e 2).
2. SELECT @@ROWCOUNT
OR SELECT ROWCOUNT_BIG()
3. NuoDB Support Windows, MacOS, Linux, Solaris, Joyent SmartOS
select len(‘a’)
For question 1, the reason is because the result is too large for the display size. In which case, the default response for SSMS is to display ‘*’ indicating that it does not have sufficient space to display the result due to the truncation on casting. To make that work, the value should first be cast to varchar with a size of at least 3, then cast again to varchar(2) OR using the SUBSTRING function to get the first two characters.
For question 2, there several options, but “SELECT ASCII(‘b’)-ASCII(‘a’)” is a very simple one.
Because varchar(2) isn’t large enough to hold the characters that make up 634.
select COUNT(‘answer’)
Puzzle1:
in “Select cast(634 as varchar(2))” we are trying to convert 3 digits to 2 char. This overflows the target string which is shown as “*” in SQL server.
At least Varchar(3) is required to display the number. Even Varchar(1) with this number will return “*”
Puzzle2:
Select len(‘a’)
will return 1
Bonus Q:
NuoDb supports 6 different Os
Hi, just saw your contest on LinkedIn. Very nice of you to sponsor. This is the best that I came up with. It can’t get too much shorter :-)
select len(‘a’)
Thanks!
Puzzle 1 —> Since 634 contains 3 digits, casting it to varchar also contains 3 digits. However, we have restricted the result length to 2. So the result length is too short to display the actual result. Hence an ‘*’ is displayed. If we increase the length to 3 or more or ommit the length (which will by default take 30 characters), it will display the proper output of 634.
Puzzle 2 —-> select len (‘a’)
You can select length of any single non-numeric character.
Puzzle 1:
It is becuase the size of the value specified(2) is not enough to hold the number(634). In this case you wont get any error.
You should in advance specify the enough length minimum 3. SELECT CAST(634 AS VARCHAR(3))
Puzzle 2:
SELECT CHARINDEX(‘M’,'M’)
My answers
Puzzle 2:
select cast (‘True’ as bit)
Puzzle 1:
* is displayed in the result set if Result length too short to display.
Another solution to Puzzle 2:
select len (‘x’)
Select (ASCII (‘b’)-ASCII(‘a’))
select Len(‘a’)
Answer 1:
When converting 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.
* Result length too short to display.
E Error returned because result length is too short to display.
Answer 2:
select len(‘a’)
1.) Whenever trying to convert character or binary expressions ( like 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 but except for some data types like here int /small int to char/Varchar . Then it finally returning *.
2.) select Len(‘a’) ,it returns the 1 value
3.) 5 types of OS ( Windows , Linux, MacOS , Joyent SmartOS , Solaris)
My second entry for puzzle 2: SELECT ’1′ — technically ’1′ is a character and not a number, just like ‘A’ :-)
Q1. * = Result length too short to display.
Q2. SELECT LEN(‘a’)
for first question, as we mention varchar (2) to typecast 3 digit number
for second question , select len(‘a’)
Q2. SELECT cos($)
Puzzle 1
–> Because [SELECT CAST(634 AS VARCHAR(2))], here length of ’634′ is 3 whereas we casted numeric value to 2 length varchar which exeeded.[SELECT CAST(634 AS VARCHAR(3))] would give 634 as output.
Puzzle 2
select COUNT(‘A’)
Bonus Q
NuoDB supports for 5 OSs Windows, MacOS, Linux, Solaris, and Joyent SmartOS
Puzzle 1: 634 has 3 characters and cannot be converted by SQL to 2 chars. * means error
Puzzle 2: SELECT (ASCII(‘B’)-ASCII(‘A’))
select len(‘a’)
The answer to your query is: “Historical reasons”
When integers are implicitly 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 (*).
The datatypes INT and VARCHAR are older than BIGINT and NVARCHAR. Much older. In fact they’re in the original SQL specs. Also older is the exception-suppressing approach of replacing the output with asterisks.
Later on, the SQL folks decided that throwing an error was better/more consistent, etc. than substituting bogus (and usually confusing) output strings. However for consistencies sake they retained the prior behavior for the pre-existing combinations of data-types (so as not to break existing code).
So (much) later when BIGINT and NVARCHAR datatypes were added, they got the new(er) behavior because they were not covered by the grandfathering mentioned above.
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.
Hi Team we have more than 300 comments. The winners are to be announced on 1st but looking at all the various entries – we will have all the comments will be visible on before 1st Oct.
However, the announcement of the winner may take 2-3 days extra as each of the 300 entries have to be evaluated and the winner is to be selected after careful evaluation.
Tomorrow is the last day to take part in the Quiz!
Puzzel 1 Answer-
SELECT CAST(634 AS VARCHAR(2))
This displays result as a * (Star) because varchar max length is 2 and the above statement casting 3 digit integer value into character which max length is 2
Hello sir
i am eagerly waiting for seeing all the comments. when it is visible for us sir.
Thank you
Lakshmi Sridhar
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 http://msdn.microsoft.com/en-us/library/ms187928.aspx)
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 http://msdn.microsoft.com/en-us/library/ms187928.aspx)
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 (http://msdn.microsoft.com/es-es/library/ms187928.aspx / 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: http://aprendiendosqlserver.blogspot.com.es/2012/09/puzzles-en-t-sql-te-atreves-encontrar.html
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: http://msdn.microsoft.com/en-us/library/ms187928.aspx
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())
couple of way
1. select count(*)
2. select len(‘a’)
a. Truncation. The length of the number (3) as a string exceeds the 2 character limit specified
b. select @@ROWCOUNT
c. Five supported OS’s
*answer For First Question is”Because we select nvarchar(2)..that store only
tow charcter
*answer for second Question is “select LEN(‘a’))
select len(‘a)
select cast(634 as varchar(2))
means the length of the integer value is greater than the converting value that time it is showing the result.
select len(‘a’)
Bonus Q:
5 operating system
SELECT @@Option/@@Option
Klaine07,
I tried SELECT @@Option/@@Option,
and I faced this error
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@@Option”
please try this:
select @@OPTIONS/@@OPTIONS
Yes Nitin, its working now, thanks for sharing.
Thanks
Manish
SELECT CAST(‘TRUE’ AS BIT)
(1) VARCHAR(2) isn’t big enough for three digit int, and conversion from int to VARCHAR which doesn’t fit returns * instead of truncating
(2) select (count(*))
(3) 5
Hey Pinal, I am exited and desperate to know the result, and its already Oct 1st started in India, Please share the results as early as possible.
Hey Pinal, I am exited and desperate to know the result, and its already Oct 1st in India, Please share the results as early as possible.
Thanks
Manish
Hi All,
There are total 350+ entires. The contests is over but it will take some more time to decide the winner because – well, if you will read all the comments, you will understand why – there are great great answers and excellent learning!
I will announce the date and winners very soon!
Kind Regards,
Pinal
Hi Pinal,
So far comments is not displaying, winner will announced later but comments will be display so that every one can read these.
Thanks
Mahesh Kumar
All the comments are displayed!
Pingback: SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1 « SQL Server Journey with SQL Authority
SELECT ’1′
the reason for using ’1′ (and I got some slack for using ’1′) is because technically ’1′ is NOT a number it is a char! so select ’1′ does reproduce the requested output, and if you where using ado to set to int sql server likes to do something called implicit casting.
SELECT ’1′
the reason for using ’1′ (and I got some slack for using ’1′) is because technically ’1′ is NOT a number it is a char! so select ’1′ does reproduce the requested output, and if you where using ado to set to int sql server likes to do something called implicit casting.
Hi All,
We thank you for your kind participation. Here is the winner of Amazon USD 50 Gift Card
Jaime Mtnz Lafargue (@jaimeml)
http://blog.sqlauthority.com/2012/09/20/sql-server-2-t-sql-puzzles-and-win-usd-50-worth-amazon-gift-card-and-25-other-prizes/#comment-353572
Jaime, please send me email at pinal at SQLAuthority.com .
The first 25 downloads of Nuodb will automatically receive their gift cards.
We thank you for your participation and appreciate your support. We will come up with another interesting puzzle very soon!
Kind Regards,
Pinal
Hi pinaldave. Why is my answer not correct? I answer sooner than Jaime.
“This is my answer. Question 1: When converting number to character type, the result display * if result length too short to display.
Question 2: select cos($)”
Hi Doan,
It is not about one answer correct and another not correct. We put all the good answers in a single table and selected random winner. As there was one winner Jaime got selected.
I have personally great respect for your answer too. As I said it is a randomly picked correct answer. Additionally there was the deadline to submit the answer – this means all the answers are equally eligible irrespective to when it was submitted.
I hope you understand.
Kind Regards,
Pinal
Hi all,
I’m very glad I won the contest. I’ve already received my Amazon.com gift card.
Thank You Pinal, NuoDB and everybodoy involved.
Kind Regards,
Jaime