SQL Strange result

Question

Hi, I found the following query in the google. DECLARE @phone_no varchar(8) SET @phone_no=910034568 SELECT @phone_no as phone_no Result is * Can anyone explain?

Aakash Maurya · Answer

In SQL results, * means Result length too short to display. This happens when we try converting Int to Char or char to int and similar to this and due to any reason auto type coversion is not possible. I found the official documentation here https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#truncating-and-rounding-results Let me know if you need more explanation.

Aakash Maurya · Answer

Hi, the reason is straigh forward. You have declared the @phone_no with size 8. And you are assiging the value of size 9 (910034568). Count the lenght. Either you delete one digit from phone number or change the declaration to DECLARE @phone_no varchar(9) It will work. Also, you have declared the data type of @phone_no as varchar and you are assigning integer value. So you can also change the assign statement as SET @phone_no = '910034568' Then it will give you output as 91003456 As the size is defined as 8.

Kapil Singh Kumawat · Answer

The result is * because you are assigning the data with longer length which is large to fit into it and you have defined varchar(8) which cannot hold this value. Solution is wither increase the datatype length or reduce the value.

Bryian Tan · Answer

"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 (*)." https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

Sai Reddy · Answer

Thank you Aakash Morya, I am clear with your answer but, in my question the result is * .