1

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?

paparazzo
  • 44,497
  • 23
  • 105
  • 176
Anj
  • 95
  • 1
  • 2
  • 11
  • Bad title. You need to remove trailing 0 from a varchar. – paparazzo Jun 16 '16 at 19:53
  • 1
    Remember to store your decimal field as decimal not varchar – Chuck Jun 16 '16 at 19:53
  • 1
    Possible duplicate of [Remove trailing zeroes using sql](http://stackoverflow.com/questions/14045994/remove-trailing-zeroes-using-sql) – Tab Alleman Jun 16 '16 at 19:59
  • Does this answer your question? [Remove trailing zeros from decimal in SQL Server](https://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server) – JumpingJezza Feb 24 '22 at 03:31

3 Answers3

3

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.######')
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
2

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

JamieA
  • 1,984
  • 4
  • 27
  • 38
  • The column values will always be 8 digit ,and only one digit before decimal point and 7 digits after decimal points. If there is any trailing zeros , I want to cut it , other wise not. For example, if the column value is 00375000, then the result should be 0.0375 (i do not need the trailing zeros after 5). If the column value is 00375002, i need the output as 0.0375002, If the column value is 00375040, I neeed the output as 0.037504 – Anj Jun 16 '16 at 19:55
  • @AnjanaH Then add that to your question] – paparazzo Jun 16 '16 at 20:04
  • @Paparazzi This column value is coming from the flat file and when I store it in sql server, it stores as varchar data type . I need to do data convertion – Anj Jun 16 '16 at 20:07
  • @AnjanaH Add that to the QUESTION – paparazzo Jun 16 '16 at 20:10
1

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

--Result will be 2.256

Eng-Mohamed Abdulwahab
  • 51
  • 3