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
It is because not enclosing between apostrofes the assignment of the number to the variable @phone_no does an implicit conversion that makes it number
Putting 9 digit number into Char(8). Hmm some sort of overflow result = 42/0x2A, off the top of my head.
It truncated all because of
varchar size specified is less than assigned value.
and because of it is integer value not varchar ‘910034568’
Converting numeric to datatype varchar
Hello Pinal ,
Hope you are doing good.As usual you are the best .
For Varchar we have to mention the values in single quotes(‘ ‘).If we mention them in single quotes we will get the value 910034568,You have given the size as 8 but provided 9 no’s.SO final output will be 91003456 for varchar(8)
Because we are assigning numeric to varchar. first it treat 910034568 as integer and converting to char. When converting to char if it is near about tolerance level it will give *, if more than that it will give arithmetic overflow error.
We are declaring the variable as varchar(8) and we are storing numeric value which is 9 char.
if it is NVARCHAR the above query returns arithmetic overflow exception
when we try to convert/cast integer to a smaller string then it will return asterisks(*) means an error.
SELECT CONVERT(VARCHAR(8),910034568) –>– output *
SELECT CONVERT(VARCHAR(5),910034568) –>– output *
SELECT CAST(910034568 AS VARCHAR(8)) –>– output *
SELECT CAST(910034568 AS VARCHAR(5)) –>– output *
This is due to int 9 chars converted into varchar 8 which is smaller and wouldn’t be trimmed insetad of its asterisk
maybe cause you are filling the variable with an integer instead of a numeric string like =’08887786′ ? Haven’t tried but…
This is due to the way the SQL server used to handle arithmetic overflow. If you change the datatype to the newer datatype nvarchar(8), you get the expected overflow error.
The older data types (eg int and varchar) used to throw a * instead of giving useful errors. In order to not break the codebase, this behaviour has been grandfathered in.
Variable is Varchar with max length of 8 and while the set for phone_no is 9 characters. * indicates that value for phone_no is too many characters for the variable.
This behavoir is related to CAST and CONVERT from one data type to another.
There is a section in MS Docs: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#truncating-and-rounding-results
* = Result length too short to display. E = Error returned because result length is too short to display.
The reason for this is because the varchar and int datatypes are original datatypes of the first SQL Server and the asterisk was then the outputted error. It sounds like later on, as they added new data types; such as nvarchar and bigint, they’d show error messages. For the sake of backwards compatibility, they kept the asterisk for the original datatypes. Shown below, if you change the varchar to nvarchar, you will get the overflow exception.
DECLARE @phone_no varchar(8)
SET @phone_no = 910034568
SELECT @phone_no as phone_no
–output: *
DECLARE @phone_no nvarchar(8)
SET @phone_no = 910034568
SELECT @phone_no as phone_no
–output: Arithmetic overflow error converting expression to data type nvarchar.
the value of phone_no is 9 while we declared the variable as a varchar 8. If we had added one more digit to phone_no we would have had an arithmetic overflow. Strange behavior i agree
Too many characters
Implicit conversion from int to too small string. Asterisk means an error occurred.
Since we are trying to save int value to Varchar(8), The implicit conversion takes place as follows–> “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 (*).”
Phone number is VARCHAR, So it needs to be inside the single quotation mark. Like ‘910034568’.
Try this – DECLARE @phone_no varchar(8)
SET @phone_no=’910034568′
SELECT @phone_no as phone_no