the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead? I have many situation where I need to convert big float numbers with many decimal places to a rounded number with 2, or 1 or no decimal places at all.

the way I have been doing this is demonstrated below:

declare @t table ( [rows] float )


insert into @t 
select 1.344
union all select 32.3577
union all select 65.345
union all select 12.568
union all select 12.4333

select * from @t




Now I will do my convertion and save them all into a temp table and then we are going to look at the structure of the temp table.

begin try
   drop table #radhe
end try
begin catch
end catch


select 
[Rows Formated] = REPLACE(CONVERT(VARCHAR(50),CAST(sp.[Rows] AS MONEY),1), '.00','')
into #radhe
from @t sp


select * 
from #radhe




that is all fine, it worked great, it did what I wanted, however, when I look at the structure of the table I get the following:

use tempdb
go

sp_help '#radhe'




you can see on the picture, the varchar(8000).
can we avoid that?

I would be happy with a varchar(50)
From the docs on REPLACE():


  If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.


This implies to me that it will always be varchar(8000) or nvarchar(4000) when the input isn't a max type. So try performing an additional CONVERT after the REPLACE:

SELECT 
  [Rows Formated] = CONVERT(varchar(50),REPLACE(CONVERT(varchar(50), 
    CAST(sp.[Rows] AS MONEY),1), '.00',''))
INTO #radhe
FROM @t AS sp;