SQL SERVER – 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes

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?

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)

366 thoughts on “SQL SERVER – 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes”

1. Sanju Nannuri |

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’)

Like

• Puneeth |

when the output length exceeds the space provided, SQL resorts to shorthand

Like

2. 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”

Like

3. Creed Smith |

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.

Like

4. cmnayal |

Puzzle 1 -> SELECT CAST(634 AS VARCHAR(2))
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’)

Like

5. Gaurav |

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)

Like

6. Sindhura |

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))

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]

Like

7. Sindhura |

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))

——————

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 COUNT(*)

Method 2

SELECT COUNT(”)

Like

8. 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’)

Like

9. 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’)

Like

10. Andrew |

1. The data was truncated and * means Result length too short to display.
2. SELECT LEN(‘A’)

Bonus: 5

Like

11. Question 1 : Number characters exceed varchar character limit
Question 2 : SELECT COUNT(GETDATE())

Like

12. rossmcatee |

Question 1: Character exceed Data-types limit hence the start
Question 2: SELECT COUNT(GETDATE())

Like

13. 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

Like

14. Deepti Bhardwaj |

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.

Like

15. 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

Like

16. Kalyanasundaram.K |

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

Like

17. Sindhura |

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))

——————

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 COUNT(*)

Method 2

SELECT COUNT(”)

Bonus Q:
How many different Operating System (OS) NuoDB support?

——————
06

Like

18. Manish |

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 .

Like

19. Manish |

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

Like

20. Manish Kaushik |

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

Like

21. 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

Like

• Christoff Jacobs |

Puz : 1 , the varchar is less than the number of characters in the select
Puz :2 , SELECT COUNT(*)
Bonus Q : 5

Like

22. Sreelekha Vikram |

Hi Pinal,

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’)

Like

23. Rajendra Pawar |

Puzzle 2:
Ans :select (@@ROWCOUNT)
It returns always 1

Like

• Rajendra Pawar |

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

Like

24. Manish Kaushik |

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

Like

25. 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

Like

26. Deepti Bhardwaj |

Puzzle 2:Select len(‘/’) will return 1 without entering the number in the query.

Like

27. Manish |

Windows, MacOS, Linux, Solaris, Joyent SmartOS
supported by NuoDB

Like

28. Mohandas Pk |

———————————————–
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.

Like

29. Mahesh |

For Puzzle2 Correct answer is — SELECT LEN(‘A’)

Like

30. udaykrishna |

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)

Like

31. Ashish Jain |

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.

Like

32. Ashish Jain |

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

Like

33. Pallavi |

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)

Like

34. Prasanna |

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

Like

35. udaykrishna |

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

Like

36. Deepti Bhardwaj |

Bonus Q: Windows, MacOS , Linux and now Sun Solaris also supports NuoDB.

Like

37. Vinay Kumar |

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”.

NuoDB supports 6 Operating Systems

Like

38. 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’)

Like

39. Manish |

correcting my last query for printing 1 as output . u dnt need @@error also
just use
select @@rowcount

returns 1

Like

40. Nikhildas |

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

Like

41. Sanjay Monpara |

Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
* 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 ascii(”)
OR
select count(*)
–=================================================================================================
Bonus Q:
How many different Operating System (OS) NuoDB support?
MacOS
Windows
Linux (RHEL,Ubuntu,SuSe,Amazon Basic EC2)
Solaris
Joyent SmartOS

Like

42. Puzzle 1: * Result length too short to display.
Puzzle 2: SELECT DATEPART(m,12-MONTH(GETDATE()))

Like

43. Naveen Gupta |

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

Like

44. In Puzzle1 data is truncated so ‘*’ is displaying. SQL guarantees that only roundtrip conversions are allows.

Like

45. Mohd Shadab |

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’)

Like

46. Somnath Desai |

SELECT LEN(‘_’)

Like

47. Just a note, we have crossed over 50 comments and many of them are very valid. Great going.

Like

48. Mohd Shadab |

Like

49. Rakesh Kumar Saini |

Hi

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,

Like

50. 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.

Like

51. Gatej Alexandru |

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()

Like

52. Dawid Burek |

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.

Like

53. 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)

Like

54. Gatej Alexandru |

Hello Pinal,

Better solution len(‘A’). Only 8 character

Like

55. Nitin Pathak |

Puzzle 1:- Because the varchar(2) is not sufficient to accomodate the width of 3 digit no.
Puzzle 2:- Select len(‘a’)

Like

56. sadhu ram |

Ans-1-it is show * becuase length of varchar data type is 2.

Ans-2-
SELECT ‘a’+CAST( COUNT(‘abc’) AS VARCHAR(1))

Like

57. Naveen Gupta |

Answer for puzzle 2-

select (char(49))

This will return result 1 as expected.

Like

58. Naveen Gupta |

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

Like

59. Jayan Udayakantha |

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)

Like

60. Zubair Khalid |

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

Like

61. Vikas Yadav |

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:

Bonus Q:
Answer:-Windows, MacOS, Linux, Solaris

Like

62. vinayak jamdar |

Puzzle 1 – Answer – Result length too short to display
Puzzle 2 – Answer – select ASCII(”)

Like

63. vinayak jamdar |

Puzzle 1- Answer – Result length too short to display
Puzzle 2- Answer – select ASCII(”)

Like

64. Yogesh Nelwadkar |

select ISDATE(getdate())

Like

65. vinayak jamdar |

Puzzle 1- Answer – Result length too short to display
Puzzle 2- Answer – select ISDATE(getdate())

Like

66. Yogesh Nelwadkar |

select ASCII(”)

Like

67. SIJIN KUMAR V P |

Puzzle 1:

Why following code when executed in SSMS displays result as a * (Star)?

SELECT CAST(634 AS VARCHAR(2))
* (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.

select COUNT(*)

Bonus Q:

How many different Operating System (OS) NuoDB support?
5(They are Windows, MacOS, Linux, Solaris, Joyent SmartOS)

Like

68. Deepa |

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

Like

69. Steven Kong |

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 “*”.

Puzzle 2:
SELECT COUNT(*)

Bonus Q:
MacOS
Windows
RHEL,SuSe,Amazon Basic EC2
Ubuntu
Solaris
Joyent SmartOS

Like

70. karthikeyanp |

p1:If Target size is less than source size then it will show *
p2: select LEN(‘a’)

Like

71. 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!

Like

72. Gopinath S |

Puzle 1. Due to Truncation when converting int to varchar. This will throw error when converting to nchar, nvarchar

Puzzle 2. SELECT (SELECT 1))

Like

73. Rakesh |

Puzzle 2:
SELECT COUNT(‘A’)

Like

74. Jitendra v Patil |

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(*))

Like

75. Ashwin A |

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

Like

76. gaurang |

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

select (count(*))

Like

77. Ashish |

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)

Like

78. Aman Ullah Shaikh |

SELECT CAST(‘true’ AS bit)

Like

79. Hassan Abbasi |

SELECT CAST(‘true’ AS bit)

Like

80. Ashish |

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)

Like

81. Amit Gurjar |

Select CAST(‘True’ AS Bit)

Like

82. 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 count(*)
select len(‘a’)

Thanks,
Kaushik Patel

Like

83. 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

Like

84. M. Furqan |

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.

Like

85. Jignesh Patel |

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

Like

86. Partha |

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.

Q2:
Ans: select COUNT(*)

Q3:How many different Operating System (OS) NuoDB support?
Five: Windows, MacOS, Linux, Solaris,Joyent SmartOS

Partha
India

Like

87. Satyanarayana Raju Pakalapati |

Puzzle 2:
SELECT Count(‘A’) ‘Data’

Like

88. 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(\$)

Like

89. Nikhil Bhavsar |

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.

Like

90. Irfan |

1 ) SELECT CAST(634 AS VARCHAR(2))

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))

select @@ROWCOUNT

Like

91. Tushar Jain |

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)

Like

92. Ivan |

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

Like

93. 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’)

Like

94. 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
–here is what we have in edi after cycle: edi=00000003
…omitted for brevity…
–copy desired length from memory to eax
sqlservr!CXVariant::StrConvertFromI4+0x65: mov eax,dword ptr [ebx+8] ds:0023:337fcc70=00000002
–here is what we have in eax:eax=00000002
–compare results
sqlservr!CXVariant::StrConvertFromI4+0x68: 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+0x76: 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(*)

Like

95. dharmesh |

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 *.

Like

96. hardik bhadania |

* 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

Like

97. We have officially received 100 entries so far! Love the communication and few indepth answers.

Like

98. Prasanna kumar.D |

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

Like

99. Prasanna kumar.D |

Bonus Q:

How many different Operating System (OS) NuoDB support?

Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS

Like

100. 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.

Like

101. Manish Kaushik |

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

Like

102. Lakshmi Sariki |

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)

Like

103. 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.

Like

104. Lakshmi Sariki |

Answer to bonus question is 5. They are Windows, MacOS, Linux, Solaris, Joyent SmartOS

Like

105. 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.

Like

106. Hi Pinal,
1) * = Result length too short to display
2) SELECT LEN(‘a’)

Like

107. David |

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

Like

108. Ivan |

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

Like

109. Gopinath 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’))

Like

110. Ashish Khandelwal |

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.

Like

111. Onur Ak |

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

Like

112. KaushalDhora |

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

Like

113. Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

SELECT count(‘ ‘)

Like

114. Srinivasan G |

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.

Like

115. Puzzle 1:
Result length too short to display
Puzzle 2:
SELECT COUNT(*)

Like

116. Srinivasan G |

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

Like

117. sandeep kumar |

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 *.

Like

118. G Srinivasan |

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(*)

Like

119. dharmesh |

shortest code for result 1 is SELECT EXP(‘ ‘)

Like

120. Bhavatharini |

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(*)

Like

121. hardik bhadania |

select LEN(‘a’)
or
select count(*)
or
select exp(”)

Like

122. Ivan |

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

Like

123. vasim mansuri |

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(”)

Like

124. Ashish Khandelwal |

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(”)

Like

125. vijay |

SELECT CAST(634 AS VARCHAR(2))
we have taken length as 2 but 634 is having length 3 hence.It is showing *

SELECT CAST(64 AS VARCHAR(1))

Like

126. udaykrishna |

PUZZLE 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

select LEN(‘u’)

Like

127. sandilyan |

SELECT (ASCII(‘B’) -ASCII(‘A’))

Like

128. Yogesh |

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 (*).

Like

129. Kaushik Patel |

PUZZLES – 2:
select Exp(”)
select count(*)
select len(‘a’)

Like

130. Yogesh |

select len(‘a’)

Like

131. nicholas.m.good@gmail.com |

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’)

Like

132. abhishek mishra |

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(*)

Like

133. Suresh Palanisamy |

Puzzle 2: select COUNT(*)

Like

134. Suresh Palanisamy |

Puzzle 1: resulting expression too small to display then cast values that why we are getting ‘*’

Like

135. Raghavendra Kumar |

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

Like

136. The value 634 does not fit in 2 characters (CHAR(2) or VARCHAR(2)). You need at least 3 chars.

Like

• Oh! and for the second question:

select count(getdate()) –> 1

Like

137. 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.

Like

138. Cassio Roberto de Oliveira Junior |

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’)

Like

139. Patrick Piché |

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

Like

140. Friuli |

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)

Like

141. Bonus question:
Windows, MacOS, Linux, Solaris, Joyent SmartOS

Like

142. azharullahmeer |

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.

Like

143. Maruthi |

1) in select statement if we use varchar (3) it will display the 634 otherwise its is dispalying star

2) select len(‘A’)

Like

144. Guillermo Wyld |

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.

Like

145. 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.

Like

146. 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.

Like

147. Jesse Clark |

Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
* = 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.

Bonus Q:
How many different Operating System (OS) NuoDB support?
5, Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS

Like

148. Rajesh |

select (cast(‘TRUE’ as bit))

Like

149. Brian Ezell |

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

Like

150. Michael Poppers |

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.

Like

151. Sparks |

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

Like

152. Puzzle #1
collate & implicite cast
>> * = Result length too short to display

Like

153. Khaja@gmail.com |

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’

Like

154. Alex Weber |

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

Like

155. Hiral |

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’)

Like

156. 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:

Bonus question:

The supported OS are Windows, MacOS, Linux, Solaris, Joyent SmartOS.

Like

157. 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.

Like

158. Kurt Redlinger |

1 – Cast overflow error converting INT to VARCHAR returns *

2 – SELECT CAST(PI() AS BIT)

3 – 5

Like

159. 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

Like

160. 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))

Like

161. tarun negi |

Ans 1 Cast is fucntion by which we can convet the values.
Ans 2 Select Count(*)

Like

162. Amit Kumar Trehan |

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’)

Like

163. ramdas2008 |

Test

Like

164. 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

Like

165. ramdas2008 |

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

Like

166. Sumit |

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

Like

167. Alex |

1) The integer is too large to fit into the character length, so SQL returns an ASCII 42 (*)
2) SELECT CAST(‘true’ AS BIT)

Like

168. 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’)

Like

169. Mohamed Arifi |

Puzzle 1: String truncation

Puzzle 2: SELECT Length(‘L’)

Bonus Q: 5 Operating Systems

Like

170. Hozefa Unwala |

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(*))

Like

171. 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

Like

172. Hello! An interesting quiz!!

A1) The * is displayed because the resulting length is too short to display the casted value.

A2) SELECT COUNT(*)

Like

173. Azhar Iqbal |

Puzzle 1. Length should equal to 3 or more.

Like

174. jefflindholm |

select count(*)

Not sure if you got it, since I had wordpress moving me around window to window

Like

175. Siva |

Puzzle 1

* indicates “Result length too short to display”

Puzzle 2

select (1)

Bonus Q supports 5 different Operating systems

Like

176. Ric Parodi |

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!

Like

177. 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)

Like

178. 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.

Like

179. hi2u |

puzzle #2 :
select difference(‘Pinal’,’Dave’)

Like

180. Amit Wadhawan |

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)

Like

181. Michel Morais |

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

Like

182. kk |

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)

Like

183. Gerald Roberts |

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

Like

184. pegoopik |

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())))

Like

185. Thank you guys we have officially received 200+ entries.

Like

• Sashikanth |

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.

Like

186. Vijayakumar |

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

Like

187. Deepti Bhardwaj |

Answer of Bonus Q:NuoDB supports Windows, MacOS, Linux, Solaris, Joyent SmartOS operationg systems.

Like

188. chandu |

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(*)

Like

• chandu |

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

Like

189. NIkunj Patel |

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.

Like

190. Puzzle 1 : Its because of Length Overflow

Puzzle 2 : select (COUNT(*))

Like

191. 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

Like

192. 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.

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.

Like

193. 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

Like

194. Manish Kaushik |

Hi Pinal,
Second answer for Puzzle no. 2 is as follows.

select CHAR(49)

Thanks
Manish

Like

195. Mayur Banta |

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 *

————————————————————————————————

select LEN(‘a’)

Like

196. Mayur Banta |

Question 3: How many different Operating System (OS) NuoDB support?

Answer: Windows, MacOS, Linux, Solaris, Joyent SmartOS

Like

197. haushila nand tripathi |

The results for Puzzle 2:

select (ascii(‘b’)- ascii(‘a’))

Like

198. Graham Russell |

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

Like

199. Ohh I am late to this contest but would like to post the answer.

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

Like

200. kiran |

because of Over flow it showing * if we place the 3 digits then the var char (3) is ok else over flow comes

Like

201. ravinder |

Because of Overflow.

SELECT LEN(‘A’)

Like

202. Lohith |

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.

Like

203. Divas Gupta |

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

Like

204. 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(*))

Like

205. 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

Like

206. Ashton June |

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

Like

207. sheikh mohammad imran |

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))

Like

208. 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:

1. Windows
2. MacOS
3. Linux
4. Solaris
5. Joyent SmartOS

Like

209. Balamurugavel |

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.

Like

210. #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.

Like

• 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.

Like

211. Pushkar |

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(*)

Like

212. Naginder Singh |

select ascii(”) for the second quiz

Like

213. Garrett G |

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(”)

Like

214. seand |

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.

Like

215. seand |

select isnumeric(‘+’) returns 1

Like

216. Harsh |

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.

Like

217. Raushan |

SELECT LEN(‘A’)
SELECT COUNT(*)
SELECT @@ROWCOUNT
SELECT PI()/PI()
SELECT POWER(234234234, 0)
SELECT ASCII(‘B’) – ASCII(‘A’)
SELECT MONTH(‘1 JAN 1990′)

Like

218. Girijesh |

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

Like

219. Satheesh |

Puzzle1 : Because the Varchar length(2) is lesser than number of digits(3).
Puzzle 2:
SELECT ASCII(‘B’)-ASCII(‘A’)

Like

220. vamshi |

select count(*) from dual;

Like

221. 1. length of 634 is 3 so when we convert it into varchar(2) it print *
2. select count (‘abc’)
3. 5 OS supported

Like

222. Nikunj Patel |

Puzzle 2 Solutions : Select COUNT(*)

Like

223. ashish dhingra |

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’)

Like

224. vikas kumar pathak |

Puzzel-1 :
* = Result length too short to display

Like

225. vikas kumar pathak |

Puzzel -2 ans :

select count(*)

Like

226. Lakshmi Sridhar |

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’)

Like

227. Varun |

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

Like

228. 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());

Like

229. Sorry missed the bonus question.
Windows, MacOS, Linux, Solaris, Joyent SmartOS

Like

230. Jaunius |

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

Like

231. Stefano Tempesta |

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

Like

232. Vladimir Matushevskiy |

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

Like

233. The Ponderer |

select 1

Like

234. Shubham Saxena |

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.

Like

235. Abhijeet Desai |

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

Like

• Abhijeet Desai |

Bonas Q:
Ans:
NuoDB support for Windows, MacOS, Linux, Solaris, Joyent SmartOS Platforms.

Like

236. 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

Like

237. P.Anish Shenoy |

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.

The shortest code that produces results as 1 is SELECT COUNT(*)

Thanks and Regards,
P.Anish Shenoy

Like

238. P.Anish Shenoy |

Answer for bonus Q

NUODB supports Windows, MacOS, SmartOS, Solaris and Linux platforms

Thanks and Regards,
P.Anish Shenoy,

Like

239. 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

Like

240. sudhir mudpe |

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

Like

241. Hi Pinal

Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?

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(*).

Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

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

Like

242. sudhir mudpe |

3.select (len(‘S’))

Like

243. chris burton |

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

Like

244. Bob Hood |

#1: An error is being produced because 634 is 3 characters but we are converting to varchar(2)

#2: Select CHARINDEX(‘a’,’abc’)

Like

245. pallavi |

Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.

select len(‘a’)

Like

246. Kishore |

Puzzle -2:
select EXP(”)

Like

247. Kishore |

Puzzle 1:
* means Result length too short to display

Puzzle 2:
select exp(”)

Like

248. Kevin McGinn |

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

Like

249. David Wanta |

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

Like

250. Satyanarayana Regula |

Here is the query.
select len(‘a’)

Like

251. Satyanarayana Regula |

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.

Like

252. amol dhadve |

select (*)

Like

253. doug abernathy |

Question 1: The type is to short for the conversion so Sql returns an asterisk.
Question 2:Select CAST(‘True’ as Bit)

Like

254. Mark Donskoy |

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’)

Like

255. carlos reyes |

A1. Result length too short to display.
A2. SELECT db_id(‘master’)
BA. Windows, MacOS, SmartOS, Solaris and Linux platforms.

Like

256. Phillip Burger |

Arithmetic overflow.

Like

• Phillip Burger |

puzzle 2: SELECT LEN(‘A’);

Like

257. Kalpak |

select ASCII(‘A’)/ASCII(‘A’)

Like

258. sutirth patel |

select count(*)

Like

259. Girija |

Puzzle 2:

select CAST(ascii(‘o’) AS BIT)

Like

260. Sandeep Kumar |

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’)

Like

261. Arvind Judge |

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’)

Like

262. Ravindar Reddy |

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

Like

263. Sreeram |

select len(‘a’)

Like

264. Rodney Kendall |

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.

Like

265. Because varchar(2) isn’t large enough to hold the characters that make up 634.

Like

Like

267. ramakant shankar |

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

Like

268. 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!

Like

269. Sucheta J. Kothare |

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.

Like

270. Mahesh Babu |

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’)

Like

271. Moin |

Puzzle 2:
select cast (‘True’ as bit)

Puzzle 1:
* is displayed in the result set if Result length too short to display.

Like

272. Moin |

Another solution to Puzzle 2:
select len (‘x’)

Like

273. kiran |

Select (ASCII (‘b’)-ASCII(‘a’))

Like

274. kiran |

select Len(‘a’)

Like

275. KK |

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.
select len(‘a’)

Like

276. Satya Krishna |

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)

Like

277. Stefano Tempesta |

My second entry for puzzle 2: SELECT ‘1’ — technically ‘1’ is a character and not a number, just like ‘A’ :-)

Like

278. Eric Bradford |

Q1. * = Result length too short to display.
Q2. SELECT LEN(‘a’)

Like

279. for first question, as we mention varchar (2) to typecast 3 digit number
for second question , select len(‘a’)

Like

280. Eric Bradford |

Q2. SELECT cos(\$)

Like

281. 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

Like

282. Mircea Dragan |

Puzzle 1: 634 has 3 characters and cannot be converted by SQL to 2 chars. * means error
Puzzle 2: SELECT (ASCII(‘B’)-ASCII(‘A’))

Like

283. hermann cardenas |

select len(‘a’)

Like

284. Michael Walter |

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.

Like

285. Stefano Tempesta |

My third try: SELECT day(\$)

Like

286. Roger Lante |

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

Like

287. Barry Brooks |

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.

Like

288. 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!

Like

• Rohan Kevin Vartak |

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

Like

• Lakshmi Sridhar |

Hello sir

i am eagerly waiting for seeing all the comments. when it is visible for us sir.

Thank you
Lakshmi Sridhar

Like

289. sarika bandgar |

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(*)

Like

290. Rajkumar |

1. It does not truncate the numbers when casting
2. SELECT ISDATE(GETDATE())

Like

• Rajkumar |

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())

Like

• Rajkumar |

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’

Like

291. Clive |

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

Like

292. w5m |

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

Like

293. shawn |

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

Like

294. Dave |

1) VARCHAR(2) is too small, should be VARCHAR(3)
2) SELECT EXP(”)

Like

295. Irina Kahar |

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(*))

Like

296. Amit |

Hi Pinal,

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.

Select isnumeric(‘+’)

i use msdn for first answer

Like

297. Emerson Gomes |

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!

Like

298. Rohan Kevin Vartak |

select COUNT(*)
This will produce Output as 1

Like

299. Yogesh |

SELECT EXP(”)

Like

300. Sumit Gaud |

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———

Like

301. Florent K |

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))

Like

302. Todd |

1. Length of value exceeds cast data type specification.
2. SELECT LEN(‘A’);

Like

303. Abdul manzoor |

Select COUNT(GETDATE())

Like

304. jeetendra |

couple of way
1. select count(*)
2. select len(‘a’)

Like

305. 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

Like

306. *answer For First Question is”Because we select nvarchar(2)..that store only
tow charcter

*answer for second Question is “select LEN(‘a’))

Like

307. klaine07 |

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.

Like

308. klaine07 |

select len(‘a’)

Like

309. klaine07 |

Bonus Q:

5 operating system

Like

• Manish Kaushik |

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”

Like

• Nitin Pathak |

select @@OPTIONS/@@OPTIONS

Like

• Manish Kaushik |

Yes Nitin, its working now, thanks for sharing.

Thanks
Manish

Like

310. Allen McGuire |

SELECT CAST(‘TRUE’ AS BIT)

Like

311. Carl |

(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

Like

312. Manish |

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.

Like

313. Manish Kaushik |

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

Like

314. 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

Like

• 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

Like

315. d |

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.

Like

316. deanvr |

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.

Like

317. Hi All,

We thank you for your kind participation. Here is the winner of Amazon USD 50 Gift Card

Jaime, please send me email at pinal at SQLAuthority.com .

We thank you for your participation and appreciate your support. We will come up with another interesting puzzle very soon!

Kind Regards,
Pinal

Like

318. Đoàn Thanh Sa |

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(\$)”

Like

• 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

Like

319. 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

Like