the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead?
0
votes
2
answers
5111
views
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)
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)
Asked by Marcello Miorelli
(17274 rep)
Jan 11, 2017, 03:40 PM
Last activity: Sep 18, 2019, 04:55 PM
Last activity: Sep 18, 2019, 04:55 PM