Try_convert * sql server 2012 error
Why am I getting *
the result when I have two digits.?
Declare @Product TABLE(id int identity(1,1),Cust VARCHAR(25),
Product VARCHAR(20), QTY INT)
INSERT INTO @Product(Cust, Product, QTY)
VALUES('KATE','VEG',2),
('KATE','SODA',6),
('KATE','MILK',1),
('KATE','BEER',12),
('FRED','MILK',3),
('FRED','BEER',24),
('KATE','VEG',3)
select *,TRY_CONVERT(varchar(1),QTY) qty1 from @Product
source to share
This is because the resulting data type is too short to display. By msdn
Truncation and rounding results
When converting a character or binary expression (char, nchar, nvarchar, varchar, binary, or varbinary) to a different data type expression, the data may be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.
This happens when you convert from int , smallint, or tinyint
tochar /varchar
source to share
You have only specified one character in TRY_CONVERT(VARCHAR(1), QTY) qty1
.
If you change it to VARCHAR(2)
, it works:
DECLARE @Product TABLE
(
id INT IDENTITY(1, 1) ,
Cust VARCHAR(25) ,
Product VARCHAR(20) ,
QTY INT
)
INSERT INTO @Product
( Cust, Product, QTY )
VALUES ( 'KATE', 'VEG', 2 ),
( 'KATE', 'SODA', 6 ),
( 'KATE', 'MILK', 1 ),
( 'KATE', 'BEER', 12 ),
( 'FRED', 'MILK', 3 ),
( 'FRED', 'BEER', 24 ),
( 'KATE', 'VEG', 3 )
SELECT * ,
TRY_CONVERT(VARCHAR(2), QTY) qty1
FROM @Product
I'm not sure why you would like to convert QTY
to VARCHAR
, though and how INT
. The size should increase if you have one QTY = 200
.
The reason you see *
is due to truncating values that are too large for the datatype specified, as stated by @Damien_The_Unbeliever in his comment .
source to share
This is because you are explicitly trying to convert it to varchar(1)
, write like below and it will work:
select *,TRY_CONVERT(varchar(2),QTY) qty1 from Product
If you want to convert values int,Float,Real
to varchar use STR () as it allows more control over formatting. The function is STR()
defined for an approximate numeric (float) data type with a decimal point that is rounded to an integer by default.
source to share
The query returns a * because sql server returns * whenever an integer is converted to (var) char and its length is less than the specified one. You can solve your problem by switching to varchar (2). See this post Why cast / convert from int returns an asterisk for more information, why SQL returns a * instead of raising in error. In short, this is similar to the old behavior.
source to share