Post reply

Truncating trailing zeros in decimals

  • 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!

  • January 28, 2010 at 5:37 am

    #1109886

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

  • 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]

  • 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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply