SQL Server – Convert VARCHAR(8000) to VARCHAR(50)

floating pointsql serversql server 2014type conversionvarchar

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

enter image description here

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

enter image description here

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'

enter image description here

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

I would be happy with a varchar(50)

Best Answer

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;
Related Question