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:

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

Quiz 1:
Varchar(2) hold maximum of 2 characters.
So SELECT CAST(99 AS VARCHAR(2)) will show 99 and any number above 99 will show * because converting character (char, nchar, nvarchar, varchar) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.
converting 634 to varchar(2) is displayed as * because * means “Result length too short to display”

Puzzle 1 -> SELECT CAST(634 AS VARCHAR(2))
Answer—->
The output ‘*’ is showing the data overflow while conversion(high to low precedence conversion). Problem here is we have data value(i.e. 634) having more length then the defined one which is varchar(2) in above statement.
This can be fixed as

SELECT CAST(634 AS VARCHAR(3))

Puzzle 2 –> shortest code that produces results as a1 without using any numbers in the select statement
Answer –> SELECT len(‘a’)

Ans 1: We are casting 3 digit number to varchar(2) which is going out of bound thats why its giving *, if we increase it to varchar(3) it will shoe correct digit or if we pass any digit upto 99 it will show correct results. When we are converting this to varchar it is making them as three char but it can only hold 2 .

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

SELECT CAST(634 AS VARCHAR(2))

Answer :

The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.

Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’

Puzzle 2:

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

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

SELECT CAST(634 AS VARCHAR(2))

Answer :
——————

The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.

Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’

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

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.

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

SELECT CAST(634 AS VARCHAR(2))

Answer :
——————

The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.

Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’

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

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)

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)

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)

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

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

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.

Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Answer 1:
* Result length too short to display.

When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.

E Error returned because result length is too short to display.

Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release

we can do roundtrip converion for: select cast(CAST(63 AS VARCHAR(2)) as int)
but not for : select cast(CAST(635 AS VARCHAR(2)) as int)

so sql server will show * because of its too short to display
–=================================================================================================
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
select (your answer)
Answer 2:
select ascii(”)
OR
select count(*)
–=================================================================================================
Bonus Q:
How many different Operating System (OS) NuoDB support?
Answer BonusQ:
MacOS
Windows
Linux (RHEL,Ubuntu,SuSe,Amazon Basic EC2)
Solaris
Joyent SmartOS

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

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

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

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

1)Answer
When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated
* = Result length too short to display. E = Error returned because result length is too short to display

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)

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

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.

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.

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.

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.

The answer to the first question is – because this is how conversion in sql server works. If the length of converted string exceeds the desired string length, then converted string simply replaced with *.
Internally the magic happens in function sqlCXVariant::StrConvertFromI4. According to WinDbg here is what happens:
1) the abs value is taken from the converting int value;
2) loop begins
3) inside loop on each iteration argument is divided by 10 and %10 to get one digit symbol, after that number 48 is simply added to get ascii code;
4) loop ends
5) check if the argument was negative minus symbol is also added
6) perform length check of converted with desired length if it is larger then simply replace string with char 42 and call the next function CXVariantBase::StrAdjustPadding
here is how it looks like:
–this is performed inside cycle, counting number of converted bytes
sqlservr!CXVariant::StrConvertFromI4+0x4d: add edi,1
–here is what we have in edi after cycle: edi=00000003
…omitted for brevity…
–copy desired length from memory to eax
sqlservr!CXVariant::StrConvertFromI4+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(*)

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.

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”

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.

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.

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

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.

if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result

Puzzle 1 : if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.

Puzzle 1 : 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.

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.

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

puzzle 1: while converting int to varchar and given length is less then the given number in this case result is very small for display and it will show *
puzzle 2: sortest method for getting 1 without usin number
select count(*)

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

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’

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

SELECT CAST(634 AS VARCHAR(2))
Ans 1:
634 is numeric, it cant be convert/reduced to varchar with the size of 2 thats why it returns “*”
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.

SELECT CAST(REVERSE(ASCII(‘d’))AS INT) — will returns 1

Bonus Q:
How many different Operating System (OS) NuoDB support?
Nuo DB will support 5 OSs which are Windows, MacOS, Linux, Solaris, Joyent SmartOS.
notes:
I could not able to answer all questions early morning at 7 : 10 am, coz the site http://www.wordpress.com was blocked at my office.

Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans 1:
A 3 digit numeric value cannot be converted/reduced to varchar with size of 2
this is the reason the result returns “*”

Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
ANS 2:
SELECT CAST(REVERSE(ASCII(‘d’))AS INT)
— the result will returns int-1

Bonus Q:
How many different Operating System (OS) NuoDB support?
The NuoDB will support 5 OS which are Windows, MacOS, Linux, Solaris, Joyent SmartOS

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.

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 *

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

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

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…

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)

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:

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.

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

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.

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.

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.

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.

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

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

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

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:

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 .

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

Answer:

When we convert character or binary expressions like char,varchar,nchar,nvarchar to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.

In the above SELECT statement, the literal 634 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 3 bytes since there are 3 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 634 cannot fit in this size (varchar(2)) and the result will be asterisk(*).

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.

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.

Puzzle 1 —> Since 634 contains 3 digits, casting it to varchar also contains 3 digits. However, we have restricted the result length to 2. So the result length is too short to display the actual result. Hence an ‘*’ is displayed. If we increase the length to 3 or more or ommit the length (which will by default take 30 characters), it will display the proper output of 634.

Puzzle 2 —-> select len (‘a’)
You can select length of any single non-numeric character.

Puzzle 1:
It is becuase the size of the value specified(2) is not enough to hold the number(634). In this case you wont get any error.
You should in advance specify the enough length minimum 3. SELECT CAST(634 AS VARCHAR(3))

Answer 1:
When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.
* Result length too short to display.
E Error returned because result length is too short to display.
Answer 2:
select len(‘a’)

1.) Whenever trying to convert character or binary expressions ( like char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display but except for some data types like here int /small int to char/Varchar . Then it finally returning *.

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.

Puzzel 1 Answer-
SELECT CAST(634 AS VARCHAR(2))
This displays result as a * (Star) because varchar max length is 2 and the above statement casting 3 digit integer value into character which max length is 2

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

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

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

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.

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.

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

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

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!

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.

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.

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.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.