0

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)

5

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;
0
1

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' 
2
  • 1
    thanks for this answer, it is absolutely fine and correct. the point though, was not how to format a temp table, it was the result of the conversion being 8000 chars that I did not like. keep answering! – Marcello Miorelli Jan 11 '17 at 16:15
  • 1
    I agree 100%, I was more interested in why the conversion was occurring, but Aaron nailed it before I could find the answer. I decided to keep the answer just to leave another way of doing things for any future googlers, but I would definitely go with Aaron's approach. – Shaulinator Jan 11 '17 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.