A very interesting puzzle, where you see the data type conversion in action. I have previously blogged about this puzzle and had received amazing feedback about it. Lots of people realize that they do not know why this particular behaviour is happening in the SQL Server.
Based on a request from the audience and friends, I have decided to build this video based on the puzzle. Let us see if you can solve this very fundamental yet extremely interesting puzzle or not.
Here is the script which I had used in this puzzle.
-- Find your answer at -- https://blog.sqlauthority.com DECLARE @PhoneNumber VARCHAR(8) SET @PhoneNumber='123456789' SELECT @PhoneNumber AS PhoneNo GO DECLARE @PhoneNumber VARCHAR(8) SET @PhoneNumber=123456789 SELECT @PhoneNumber AS PhoneNo GO
When you run the code above you will get a result similar to the following. While we all expected that the value will be truncated but it actually displays us a *. The question is why star and no other value.
Leave your answer in the comment section. If you want a hint, I have included that in the video. If you want an answer, you can find that on this blog only by searching for data type keyword it there. I am not going to give you a direct link, you have to work for it.
You can subscribe to my YouTube channel for more fun stuff.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I found it in the Microsoft documentation for cast and convert! This has to do with truncation and according to the documentation, values that are too short to display so converting from an int to a varchar will display an ‘*’.
Thanks for sharing an interesting puzzle and behavior. You had explained this on your page https://blog.sqlauthority.com/2018/03/06/sql-server-puzzle-sql-server-stored-procedure-unexpected-results/
This is because varchar is not large enough to store the value and hence it shows asterisk.