# Mass Value changed on Value after float point When convert from float to varchar? RSS

Last post Nov 08, 2020 11:52 AM by mgebhard

• ahmedbarbary

Member

38 Points

350 Posts

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

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```

• mgebhard

All-Star

52091 Points

23222 Posts