We all love brain teasers and interesting puzzles. Today I decided to come up with 2 interesting puzzles and winner of the contest will get USD 50 worth Amazon Gift Card. The puzzles are sponsored by NuoDB. Additionally, The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Bonus Q:
How many different Operating System (OS) NuoDB support?
Click here HINT
If you can solve above puzzles you will be eligible for winning USD 50 Amazon Gift Card. However, you can always enroll yourself for following Bonus Prizes where if you have good chance of winning USD 10 Amazon Gift Card (if you are first 25 individual in specific time).
Bonus Prizes:
The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
Rules:
- Please leave an answer in the comments section below.
- You can resubmit your answer multiple times, the latest entry will be considered valid.
- The winner will be announced on 1st October.
- Last day to participate in the puzzle is September 28th, 2012.
- All valid answer will be kept hidden till September 28th, 2012.
- Only One Winner will get USD 50 worth Amazon Gift Card.
- The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
- The winner will be selected using random algorithm.
UPDATE: The winner of the contest is Jaime Mtnz Lafargue (@jaimeml)
Reference: Pinal Dave (https://blog.sqlauthority.com)
365 Comments. Leave new
PUZZLES -1:
As we all have seen that it is very common to convert values from one data type to another while working with data. Most of the time these conversions are achieved using implicit conversion approach. There are two ways of converting values of one data type to another data type and they are referred to as Explicit Conversion and Implicit Conversion. Implicit conversion is achieved by using a value as it is already a desired type of data. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. In order to convert a value from one data type to another data type explicit conversion should be used using CAST or CONVERT functions.
When we convert character or binary expressions like varchar to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.
First Case:
view sourceprint?1.SELECT CAST (634 AS VARCHAR(2))
In the above SELECT statement, the literal 2004 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 4 bytes since there are 4 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 2004 cannot fit in this size (varchar(2)) and the result will be asterisk(*).
Second Case:
view sourceprint?1.SELECT CAST (‘634’ AS VARCHAR(2))
In the above SELECT statement, as per the BOL, the conversion to varchar will be truncated to get the result of the length specified in target type. Generally, CAST function converts any expression of type char, nchar, nvarchar, varchar, binary, or varbinary to an expression of any type can convert without any errors. But in the above example, CAST function truncates to the specified length of 2 used in the target type length and it does not fit into the length specified, therefore, CAST function truncates 2004 last two digit (04) and only first two digits (20) are returned. Therefore, the second select statement returns the value of 20 using CAST statement.
PUZZLES – 2:
select count(*)
select len(‘a’)
Thanks,
Kaushik Patel
PUZZLES -1:
As we all have seen that it is very common to convert values from one data type to another while working with data. Most of the time these conversions are achieved using implicit conversion approach. There are two ways of converting values of one data type to another data type and they are referred to as Explicit Conversion and Implicit Conversion. Implicit conversion is achieved by using a value as it is already a desired type of data. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. In order to convert a value from one data type to another data type explicit conversion should be used using CAST or CONVERT functions.
When we convert character or binary expressions like varchar to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.
First Case:
view sourceprint?1.SELECT CAST (634 AS VARCHAR(2))
In the above SELECT statement, the literal 2004 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 4 bytes since there are 4 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 2004 cannot fit in this size (varchar(2)) and the result will be asterisk(*).
Second Case:
view sourceprint?1.SELECT CAST (’634′ AS VARCHAR(2))
In the above SELECT statement, as per the BOL, the conversion to varchar will be truncated to get the result of the length specified in target type. Generally, CAST function converts any expression of type char, nchar, nvarchar, varchar, binary, or varbinary to an expression of any type can convert without any errors. But in the above example, CAST function truncates to the specified length of 2 used in the target type length and it does not fit into the length specified, therefore, CAST function truncates 2004 last two digit (04) and only first two digits (20) are returned. Therefore, the second select statement returns the value of 20 using CAST statement.
PUZZLES – 2:
select len(9)
select count(*)
select len(‘a’)
Thanks,
Kaushik Patel
Hi guys,
P1: This is because the result is being truncated and the result is too short to display.
P2: SELECT CAST(‘true’ AS bit)
Thanks.
1) select count(‘This is The Answer of First Question’)
2) explicit casting result length too short to display for 634 in just 2 char
answer for bonus que is
nuoDB supports following os Windows, MacOS, Linux, Solaris, Joyent SmartOS
Q1:SELECT CAST(634 AS VARCHAR(2))
If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to an int), SQL Server generates an error message.
When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default
When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.
https://www.microsoft.com/en-us/download/details.aspx?id=51958
Q2:
Ans: select COUNT(*)
Q3:How many different Operating System (OS) NuoDB support?
Five: Windows, MacOS, Linux, Solaris,Joyent SmartOS
Partha
India
Puzzle 2:
SELECT Count(‘A’) ‘Data’
Hi,
This is my answer. Question 1: When converting number to character type, the result display * if result length too short to display.
Question 2: select cos($)
1. Because conversion from 634 does not fit to varchar(2). The culprit is the size 2.
2. Select Count(‘x’)
Bonus : NuoDB support 5 different OS. Windows, MacOS, Linux, Solaris, Joyent SmartOS.
1 ) SELECT CAST(634 AS VARCHAR(2))
ANSWER
the result length is more than 2 characters that’s why it display *… this rule will apply to all casts where result then is greater then specified length
like SELECT CAST(10 AS VARCHAR(1))
2) ANSWER
select @@ROWCOUNT
1.In sql server when you try to convert int data types to char or varchar and result is too short to display then it displays ‘ * ‘.
2. Select count(‘A’) will return 1 as output
Bonus question answer is NuoDb Supports 5 OS (Windows, MacOS, Linux, Solaris, Joyent SmartOS)
Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.
Puzzle 2: SELECT COUNT(*)
Bonus Puzzle: 5 different OS’s
Puzzle 1 : When casting data size is greater than the allocated varchar data type, the result will be shown as star. This also indicates the allocated size is not sufficient for casting
Puzzle 2 : SELECT LEN(‘a’)
Hi!
The answer to the first question is – because this is how conversion in sql server works. If the length of converted string exceeds the desired string length, then converted string simply replaced with *.
Internally the magic happens in function sqlCXVariant::StrConvertFromI4. According to WinDbg here is what happens:
1) the abs value is taken from the converting int value;
2) loop begins
3) inside loop on each iteration argument is divided by 10 and %10 to get one digit symbol, after that number 48 is simply added to get ascii code;
4) loop ends
5) check if the argument was negative minus symbol is also added
6) perform length check of converted with desired length if it is larger then simply replace string with char 42 and call the next function CXVariantBase::StrAdjustPadding
here is how it looks like:
–this is performed inside cycle, counting number of converted bytes
sqlservr!CXVariant::StrConvertFromI4+0x4d: add edi,1
–here is what we have in edi after cycle: edi=00000003
…omitted for brevity…
–copy desired length from memory to eax
sqlservr!CXVariant::StrConvertFromI4+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(*)
when Result is too short to display so sql give the star(*) sign as result.here in this question casting of int to varchar(2) so its give the result as *.
* is displayed because we are casting 634 int to varchar(2) that is result is too short to display….so it is giving * because varchar(2) is too small and we are converting int to varchar…..thank you
Dear Pinal,
The answer for today puzzle are
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans : Result length too short to display.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Ans : SELECT COUNT(*)
Regards,
Prasanna kumar.D
Bonus Q:
How many different Operating System (OS) NuoDB support?
Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1 –
SELECT CAST(634 AS VARCHAR(2)) –
This statement should convert 634 into VARCHAR(2) datatype. So the expected result is “634”. But the target datatype’s size is 2, which is less than the size of the actual result (which is 3 here). So the result needs to be truncated. The * here just represents the truncation of the resultant data.
Puzzle 2 –
Displaying 1 without using a number –
SELECT count(”)
Bonus Q –
NuoDB is an amazing product with awesome concept of cloud database. It has support for all major OSes like – Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Hi Pinal,
For Question no. 2 (Write the shortest code that produces results as 1 without using any numbers in the select statement.)
The query is
select ASCII(‘b’)-ASCII(‘a’);
Thanks
Manish
1) Answer for first question is
Result will be * in all the cases when the length of the integer is more than the casting length defined for char/varchar
2) Answer for second question is
SELECT CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)/CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)