CODEWITHSUNDEEP
sql-server
Anj
Anj

Reputation: 95

Remove the trailing zeros after Decimal points without truncating/ approximating the value in SQL server

Have decimals stored as varchar.

I have a column with value 0.0375000. I need to convert this into 0.0375.

When I did 

convert(decimal(8, 7), substring(column, 0, 1) + '.' + substring(column, 2, 8)))

I got the result as 0.0375000.

I want to remove all the trailing zeros and the result I want is 0.0375

How can I do this?

Upvotes: 1

Views: 14527

Answers (3)

Eng-Mohamed Abdulwahab
Eng-Mohamed Abdulwahab

Reputation: 51

--SQL Code for easy removing trailing zeros. select CONVERT(DOUBLE PRECISION,'2.256000')

--Result will be 2.256

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

If 2012+ The #'s indicate an optional display

Select format(0.0375000,'0.######')  Returns 0.0375

Select format(0.037502,'0.######')  Returns 0.037502

Sorry didn't see stored as varchar()

Select format(cast(somecolumn as decimal(18,8)),'0.######')

Upvotes: 3

JamieA
JamieA

Reputation: 2013

if you only need 4 decimal places, you want decimal 5,4 (assuming your number to the left of the decimal point fits into 1 digit , if you need 2 digits, choose decimal(6,4) for example )

select  convert(decimal(5,4), substring(column,0,1)+'.' +substring(column,2,8) )

decimal data type https://msdn.microsoft.com/en-gb/library/ms187746.aspx

Upvotes: 2

Related Questions