Today’s puzzle is going to be an interesting puzzle for many. The matter of the fact when I discovered this puzzle related to the stored procedure and unexpected results, I was very much surprised as well.
Let us see the puzzle right away.
Unexpected Results
First, let us following code where I have declared varchar and right after that I am storing some value in it and selecting it afterwards.
DECLARE @phone_no varchar(8) SET @phone_no=910034568 SELECT @phone_no as phone_no
When you run the above code, it gives us the following strange results.
Here is the Puzzle: Unexpected Results
When we run above query, why is it resulting result as *, instead of the value stored in a variable?
Please leave your answer in the comments section and I will publish the answers next week at the same time. I believe this puzzle is simple enough but not as easy as you think. I suggest you may ask this question to your work colleagues and see if they know the answer to this puzzle.
Here are few additional puzzles, if you want to try to tease your brain:
- SQL SERVER – Puzzle – How Does Table Qualifier Work in INSERT Statement?
- SQL SERVER – Simple Puzzle with UNION – Part 2
- SQL SERVER – Puzzle – Strange Behavior With Extra Comma in Create Table Statement
- SQL Puzzle – A Quick Fun with BitWise Operator
Update: The correct answer to this puzzle is given by Shweta in the comments section.
Please feel free to reach out to me on twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
50 Comments. Leave new
So VARCHAR is holding only the characters you assigned to it and that’s why retrieve as *, which means there are different amount of characters than 8 :)
You have specified a string as Varcha(8) and your length is 9 digits, then you got the * as result, because you are not specifing the digits into a quotes E.i.(SET @phone_no=’910034568′). Then your result would be for example: [91003456]
However if you specify the correct length as Varchar(9) and your Set is like SET @phone_no=910034568 then you get the correct result.
So VARCHAR is holding only the amount of characters you assigned to it and that’s why retrieve as *, which means there are different amount of characters than 8 :)
You have specified a string as Varcha(8) and your length is 9 digits, then you got the * as result, because you are not specifying the digits into a quotes E.i.(SET @phone_no=’910034568′). then your result would be for example: [91003456]
However if you specify the correct length as Varchar(9) and your Set is like SET @phone_no=910034568 then you get the correct result.
The size of varchar declared is 8 while the phone no is 10 digits
There is a implicit conversion of integer to varchar and it returns back an * implying that an error has occurred.
You have defined less length than actual variable value length. that’s why this type of result you got.
implicit conversion failed due to 9 character in input as variable declared as varchar(8) which caused the result to *
Pinal could you explain this. Have we got the right answer?
This is implicit conversion issue when you are trying to convert 9 digits of number to varchaar which resulted in output as * .But when we write same query with number in single quote ‘910034568’ we can see output as 91003456.
* means everything. This is an overflow situation where all of the available destination varchar field is used, and then some. If you assign it with single quotes, the string assignation left justifies and truncates, but that never gets called on assignation of the implicit conversion from int to varchar(8).
It makes perfect sense. Justification and truncation of a string still gives a fair representation of the value, but doing it for a number means that it could be many factors of 10 incorrect. It’s far better to throw an error: For example:
DECLARE @phone_no varchar(8)
SET @phone_no=910034568
SELECT CONVERT(int, @phone_no) as phone_no
Gives:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ‘*’ to data type int.
Imagine if that number went on to used in an interest calculation on your savings. If it was a factor of 10 out, it’s better it causes a problem at source, rather than a factor of 10 problem in your savings! ;-)
Refer:https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017
Converting integer data
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 (*).
Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type. The following example shows that when the threshold value is exceeded, the data type of the result changes from an int to a decimal.