the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead?

Question

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)

Answer 1

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;

Answer 2

When I explicitly declare the temp table, it forces varchar(50), it does however force the scalar operator of CONVERT_IMPLICIT. Aaron has a much better fix.

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

BEGIN TRY
    DROP TABLE #radhe
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #radhe
    (
        [Rows Formatted] VARCHAR(50)
    )
INSERT  INTO #radhe
        (
            [Rows Formatted]
        )
        SELECT
            [Rows Formated] = REPLACE(CONVERT(VARCHAR(50) , CAST(sp.[Rows] AS MONEY) , 1) , '.00' , '')
        FROM
            @t sp

SELECT
    *
FROM
    #radhe

USE tempdb
go

sp_help '#radhe'