How to remove last two number ending after float point SQL server 2012?

Question

I work on SQL server 2012 and I need to remove Last Two digit After Float Point

table already have big data and this is sample as below

max number after float point is 6 and at least one number after float point

if i have one digit After Float Point nothing do

So How to do that ?

create table #temp
(
Numbers float
)
insert into #temp(Numbers)
values
(155.7865),
(708.986517),
(200.333),
(555.3)

select * from #temp

Expected Result

Numbers
155.78
708.9865
200.3
555.3

What I have tried:

select round(Numbers,2),* from #temp

Answer 1

Hi @ahmed salah ,

Please refer below:

  ;with cte as (  
   select CONVERT(VARCHAR(50), Numbers,128) Numbers from #temp)  
    select   
 case when LEN(Numbers)-CHARINDEX('.',Numbers)>2 then Convert(float,left(Numbers,LEN(Numbers)-2))  
      when LEN(Numbers)-CHARINDEX('.',Numbers)=2 then Convert(float,left(Numbers,LEN(Numbers)-1))  
  else Convert(float,Numbers) end Numbers  
  from cte  

Output:
Numbers
155. 78
708. 9865
200. 3
555. 3

Or still using round:

  ;with cte as (  
    select Numbers Number,CONVERT(VARCHAR(50), Numbers,128) Numbers from #temp)  
  
  select case when len(Numbers)-CHARINDEX('.',Numbers)>2 then Convert(float,ROUND(Numbers,len(Numbers)-CHARINDEX('.',Numbers)-2))  
              when len(Numbers)-CHARINDEX('.',Numbers)=2 then Convert(float,ROUND(Numbers,len(Numbers)-CHARINDEX('.',Numbers)-1))  
			  else Convert(float,Numbers) end Numbers  
  from cte  

Output:
Numbers
155.79
708.9865
200.3
555.3

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

yes my case is 3.14 become 3.14159
so i need it exactly 3.14 not rounding
i need numbers exactly

Answer 3

my issue is numbers on temp table is float but when
i use it changed rounding it

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

see result

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

number on temp table as 580.28613 but when i merge it on strmass it become 580.286
so when the remaining numbers 13 gone this is my question
58028613 become 580286 why
so how to solve this issue please