Mass Value changed on Value after float point When convert from float to varcha?

Question

Mass Value changed on Value after float point When convert from float to varcha?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

November 7, 2020 at 6:27 pm

I work on SQL Server 2012. When using Stuff to collect data separated by comma, I get strange results. Mass number separated by comma in table #tmpParts

Not exactly what exist in the original table #TempPC.

To summarize my issue mass for part 5223986-2 for aluminium

is 580.28613 in table #TempPC

is 580.286 in table #tmpParts

Why values changed for mass and how to separated by comma exactly as input on mass values #Temppc .

Also another value changed

CREATE TABLE #TempPc
(
    PartNumber nvarchar(50),
    Substance  nvarchar(100),
    Mass Float,
)

INSERT INTO #TempPc (PartNumber, Substance, Mass)
VALUES 
('5223986-2', 'Copper', 33.73757),
('5223986-2', 'Zinc', 12824.526),
('5223986-2', 'Aluminum', 580.28613),
('5223986-5', 'Copper', 33.73756),
('5223986-5', 'Zinc', 12824.52563),
('5223986-5', 'Aluminum', 580.28612)

CREATE TABLE #tmpParts
(
    id INT IDENTITY,
    PartNumber nvarchar(50),
    cnt INT,
    strSubstances NVARCHAR(MAX),
    strMass NVARCHAR(MAX)
)

INSERT INTO #tmpParts (PartNumber, cnt)
    SELECT  
        t.PartNumber, COUNT(t.Substance)
    FROM #TempPC t
    GROUP BY t.PartNumber
                           
UPDATE p
SET p.strSubstances = CAST (STUFF((SELECT ',' + CAST(t.Substance AS VARCHAR(3500))
                                   FROM #TempPC t
                                   WHERE t.PartNumber = p.PartNumber
                                   ORDER BY t.Substance
                                   FOR XML PATH('')), 1, 1, '') AS NVARCHAR(3500)),
    p.strMass = CAST (STUFF((SELECT ',' + CAST(t.Mass AS VARCHAR(3500))
                             FROM #TempPC t
                             WHERE t.PartNumber = p.PartNumber
                             ORDER BY t.Mass
                             FOR XML PATH('')), 1, 1, '') AS NVARCHAR(3500))
FROM #tmpParts p

But I get wrong result on mass because it display data two parts same mass separated by comma on two parts .

But in the table #TempPC two parts are different on mass value:

id  PartNumber  cnt strSubstances           strMass
1   5223986-2   3   Aluminum,Copper,Zinc    33.7376,580.286,12824.5
2   5223986-5   3   Aluminum,Copper,Zinc    33.7376,580.286,12824.5

Final Result I need Same Numbers on #TempPC with Comma Separated on Table #tmpParts ON strMass :

id  PartNumber  cnt strSubstances              strMass
1   5223986-2   3   Aluminum,Copper,Zinc    33.73757,580.28613,12824.526
2   5223986-5   3   Aluminum,Copper,Zinc    33.73756,580.28612,12824.52563

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18866 More actions · Answer 1

Instead of:

CAST(t.Mass AS VARCHAR(3500))

try:

STR(t.Mass, 11, 5)

You may then want to look at getting rid of trailing zeros.

This reply was modified 4 years, 2 months ago by Ken McKelvey.

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 2

My first question would be, why are you using FLOAT to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

I began my career decades ago, writing Fortran and scientific applications. We had to spend two weeks in our Fortran class learning how floating-point numbers at work. Floating-point numbers are also known as "approximate datatypes" because they do funny things with rounding and computations. If you don't need to use floating-point, then use DECIMAL (S, P), or NUMERIC (S,P) datatypes instead. Technically, there is a difference between those two datatypes, but Sybase treats them the same and I believe Microsoft is still preserving that convention. They are exact numeric datatypes and will not have strange rounding errors. I probably ought to post a good source for understanding floating-point, but the last one I remember was Tom Ochs articles decades ago.

Please post DDL and follow ANSI/ISO standards when asking for help.