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
SELECT(CHAR(49))
1. The result is truncated because varchar(2) is too short to display the result correctly. Ref: ‘Truncating and Rounding Results’ in ‘CAST and CONVERT’ in Books Online.
2. select ascii(‘b’) % ascii(‘a’);
Bonus: 5
1) SELECT CAST(634 AS VARCHAR(2))
— If length of source is larger then specified size at the time of conversion then the * is an indicator of overflow.
2) SELECT (CHAR(49))
Ans 1 Cast is fucntion by which we can convet the values.
Ans 2 Select Count(*)
Ans 1.
SELECT CAST (634 as varchar(2)) : Interprets conversion of expression from int data type to varchar
While Casting, When the input of CAST is not a character string, the output has the default collation of the database, and a collation label of coercible-default. In such case, when the input is int, smallint, or tinyint and the output of CAST is varchar, the result is *
Ans 2.
Select Len(‘a’)
Test
Q1 answers is : select COUNT(*)
Q2 answers is : because varchar(2) size is less than no of digits in the following no. 634 (no. of digites are 3)
Q3 answers is : 5 os
SELECT CAST(634 AS VARCHAR(2))
returns * because of not enough characters in VARCHAR, VARCHAR(3) would return the value 634.
SELECT (ASCII(‘B’)-ASCII(‘A’)); would return a value of 1.
Name: Ramdas
Location: Charlotte,NC,USA
Q1)
Ans. It is showing * star because of error.
If we replace 634 with any digit number from 1-99 it will show the number except * star.
But if we replace 100 den it will show * as an error but if we increase the varchar size to 3 nd den write 100 or any value till 999 it sill show the number itself but again if we write 1000 as varchar(3) it will show * as an error
1) The integer is too large to fit into the character length, so SQL returns an ASCII 42 (*)
2) SELECT CAST(‘true’ AS BIT)
Puzzle 1:
Because SQL Server returns ascii character 42 (aka ‘*’) when it implicitly tries to convert an integer that is too large for it’s character field.
Puzzle 2:
select len(‘a’)
Puzzle 1: String truncation
Puzzle 2: SELECT Length(‘L’)
Bonus Q: 5 Operating Systems
Puzzle1: When you cast 3 characters lke 634 then you need to give in Varhcar(3) not Varchar(2)…….It’s like an overflow character.
Puzzle2: select (count(*))
Puzzle 1: when converting INT, SMALLINT or TINYINT to CHAR, or VARCHAR using CAST, and the number don’t fit in the CHAR, like trying to fit 634 in 2 characters, the result is just the character *.
Puzzle 2: select SIGN(PI())
Bonus Q: NuoDB supports 5 OSs
1.
* means the result length is too short to display
see https://www.microsoft.com/en-us/download/details.aspx?id=51958
2.
select (‘1’)
— ‘1’ is not a number, but a string
Select count(”)
Hello! An interesting quiz!!
A1) The * is displayed because the resulting length is too short to display the casted value.
A2) SELECT COUNT(*)
Puzzle 1. Length should equal to 3 or more.
Select count(*) will produce a 1
select count(*)
Not sure if you got it, since I had wordpress moving me around window to window