Truncating trailing zeros in decimals

• togaratw

  Old Hand

  Points: 351

  More actions

  January 28, 2010 at 5:09 am

  #96949

  Hi All,

  How can I remove trailing zeros in decimals? For instance if the value is 12.4086000 to just return 12.4086, 7.2300 to return 7.23 etc.

  I could write a function for this but I want to find out if there's a pre-defined function in 2005 that I can use to avoid reinventing the wheel.

  Thanks!

• Chandra Sekhara Vyas Dhara

  SSChampion

  Points: 10207

  More actions

  January 28, 2010 at 5:37 am

  #1109886

  You need to write function. There is no such predefined function for your requirement.

• wschampheleer

  SSCertifiable

  Points: 5504

  More actions

  January 28, 2010 at 6:46 am

  #1109942

  Can you be more specific as to what are you trying to achieve i.e. what will you do with the result?

  I suggest you first check if you cannot simply use the formatting possibilities of your front-end tool (Excel, Reporting Services etc.)

  If that is not an option, maybe you can convert the decimal to a float (beware of rounding issues!) or convert to a string e.g.

  DECLARE @a DECIMAL(10,5)

  SET @a = 12.4086000

  SELECT @a, REPLACE(RTRIM(REPLACE(@a, '0',' ')),' ','0')

  SET @a = 7.23

  SELECT @a, REPLACE(RTRIM(REPLACE(@a, '0',' ')),' ','0')

  Beware that the latter still leaves you with the decimal point if your number is an integer. But I think I've shown you the path on how to deal with that too

  Regards,

  Willem
  http://wschampheleer.wordpress.com[/url]

• togaratw

  Old Hand

  Points: 351

  More actions

  January 28, 2010 at 8:56 am

  #1110123

  Thanks a lot for the lead.

  I suppose this can take care of the trailing decimal point for integers:

  SELECT REPLACE(REPLACE(RTRIM(REPLACE(@a, '0' ,' ')), ' ','0') + ' ', '. ', '')

  I'm just using a SELECT statement to pull up some measurements (from a field with 6 decimal place precision) and dont want to clog the result with trailing zeroes.

  Once again, thanks!