Remove trailing zeroes from Decimal Precision values in SQL Server

Last Reply on Dec 06, 2016 09:54 AM By dharmendr

Posted on Dec 06, 2016 09:51 AM

I have Item Table, the fields are

Itm_No int

Itm_Code varchar(10)

Itm_Desc nvarchar(500)

Itm_AvgUnitPrice numeric(18,4)

Item Table data is below

Itm_No    Itm_Code   Itm_Desc  Itm_AvgUnitprice

1            00001111    HP                  14.3310

2            00001112    Dell                 14.3541

3            00001113    samsung           14.1000

The o/p should be in Itm_AvgUnitprice only show the decimal which have not the zeros.

Itm_No    Itm_Code   Itm_Desc  Itm_AvgUnitprice

1            00001111    HP                  14.331

2            00001112    Dell                 14.3541

3            00001113    samsung           14.1

 

Thanks

Basit.

Posted on Dec 06, 2016 09:54 AM Modified on on Dec 06, 2016 09:54 AM

Hi basit0079,

Refer the below test query.

DECLARE @Test AS TABLE(Itm_No INT,Itm_Code VARCHAR(50),Itm_Desc VARCHAR(50),Itm_AvgUnitprice numeric(18,4))
INSERT INTO @Test VALUES(1,'00001111','HP',14.3310)
INSERT INTO @Test VALUES(2,'00001112','Dell',14.3541)
INSERT INTO @Test VALUES(3,'00001113','samsung',14.1000)
SELECT Itm_No,Itm_Code,Itm_Desc,REPLACE(RTRIM(REPLACE(Itm_AvgUnitprice, '0', ' ')), ' ', '0')Itm_AvgUnitprice FROM @Test

Output

Itm_No Itm_Code Itm_Desc Itm_AvgUnitprice
1 1111 HP 14.331
2 1112 Dell 14.3541
3 1113 samsung 14.1