+ Reply to Thread
Results 1 to 5 of 5

strip trailing zeros from decimal

  1. strip trailing zeros from decimal

    Is there an easy way to strip trailing zeros from decimals so that

    select amount from table

    gives you 2.5 instead of 2.50 or 3 instead of 3.00?

    Thanks,

    Tom



  2. Re: strip trailing zeros from decimal

    On Apr 16, 6:32*pm, "tshad" wrote:
    > Is there an easy way to strip trailing zeros from decimals so that
    >
    > select amount from table
    >
    > gives you 2.5 instead of 2.50 or 3 instead of 3.00?
    >
    > Thanks,
    >
    > Tom


    Not easily in SQL; in most cases, formatting of data elements is best
    handled by your client application.

    Stu

  3. Re: strip trailing zeros from decimal

    Just to add a few words:

    It isn't SQL Server that produces these zeroes, it is the client application. The client app
    receives binary data from SQL Server and based on the datatype decides on some presentation. So
    changing how this is presented mean you would have to return the data as some other datatype. I
    agree with Stuart that this is definitely better handled on the client app.

    --
    Tibor Karaszi, SQL Server MVP
    http://www.karaszi.com/sqlserver/default.asp
    http://sqlblog.com/blogs/tibor_karaszi


    "Stuart Ainsworth" wrote in message
    news:5eae71ad-1c26-433a-a9de-3e152bfd23dc@x41g2000hsb.googlegroups.com...
    On Apr 16, 6:32 pm, "tshad" wrote:
    > Is there an easy way to strip trailing zeros from decimals so that
    >
    > select amount from table
    >
    > gives you 2.5 instead of 2.50 or 3 instead of 3.00?
    >
    > Thanks,
    >
    > Tom


    Not easily in SQL; in most cases, formatting of data elements is best
    handled by your client application.

    Stu



  4. Re: strip trailing zeros from decimal

    Hi,

    I have just had this problem myself and after trying a few different ways, I have come across the follwing solution:

    (CAST((??.????) AS INTEGER)

    > On Wednesday, April 16, 2008 6:32 PM tshad wrote:


    > Is there an easy way to strip trailing zeros from decimals so that
    >
    > select amount from table
    >
    > gives you 2.5 instead of 2.50 or 3 instead of 3.00?
    >
    > Thanks,
    >
    > Tom



    >> On Wednesday, April 16, 2008 10:34 PM Tibor Karaszi wrote:


    >> Just to add a few words:
    >>
    >> It isn't SQL Server that produces these zeroes, it is the client application. The client app
    >> receives binary data from SQL Server and based on the datatype decides on some presentation. So
    >> changing how this is presented mean you would have to return the data as some other datatype. I
    >> agree with Stuart that this is definitely better handled on the client app.
    >>
    >> --
    >> Tibor Karaszi, SQL Server MVP
    >> http://www.karaszi.com/sqlserver/default.asp
    >> http://sqlblog.com/blogs/tibor_karaszi
    >>
    >>
    >> "Stuart Ainsworth" wrote in message
    >> news:5eae71ad-1c26-433a-a9de-3e152bfd23dcatx41g2000hsbdotgooglegroups.com...
    >> On Apr 16, 6:32 pm, "tshad" wrote:
    >>
    >> Not easily in SQL; in most cases, formatting of data elements is best
    >> handled by your client application.
    >>
    >> Stu



    >>> On Saturday, April 19, 2008 1:58 AM Stuart Ainsworth wrote:


    >>> Not easily in SQL; in most cases, formatting of data elements is best
    >>> handled by your client application.
    >>>
    >>> Stu





  5. Re: strip trailing zeros from decimal

    Sorry if this comes off as rather harsh but, your answer is useless because:
    1. It does not give the OP what he wants - he wants a decimal result, not an
    integer with the decimal portion stripped - I suggest you read the original
    message and test your proposed solution before posting it
    2. The OP posted his message 3 years ago and is not likely to be around to
    benefit from your answer
    3. The OP's question was more than adequately answered 3 years ago - read
    the entire message below.

    For your benefit, I will add: formatting a number involves converting it to
    a string. Numbers are not stored with any format. If you wish to apply a
    format to a number, you must convert that number to a string. How trailing
    zeroes are handled is a question of format, so any solution proposed for
    this question would have to involve a conversion of the number to string.
    This can be problematic if one wishes to do any subsequent sorting or
    arithmetic with the results since the strings would have to be converted
    back to numbers in order to obtain correct results.

    As Tibor correctly stated, formatting results for display should be done in
    the application that is retrieving the results.

    Annmarie Watkins wrote:
    > Hi,
    >
    > I have just had this problem myself and after trying a few different
    > ways, I have come across the follwing solution:
    >
    > (CAST((??.????) AS INTEGER)
    >
    >> On Wednesday, April 16, 2008 6:32 PM tshad wrote:

    >
    >> Is there an easy way to strip trailing zeros from decimals so that
    >>
    >> select amount from table
    >>
    >> gives you 2.5 instead of 2.50 or 3 instead of 3.00?
    >>
    >> Thanks,
    >>
    >> Tom

    >
    >
    >>> On Wednesday, April 16, 2008 10:34 PM Tibor Karaszi wrote:

    >
    >>> Just to add a few words:
    >>>
    >>> It isn't SQL Server that produces these zeroes, it is the client
    >>> application. The client app receives binary data from SQL Server
    >>> and based on the datatype decides on some presentation. So changing
    >>> how this is presented mean you would have to return the data as
    >>> some other datatype. I agree with Stuart that this is definitely
    >>> better handled on the client app.
    >>>
    >>> --
    >>> Tibor Karaszi, SQL Server MVP
    >>> http://www.karaszi.com/sqlserver/default.asp
    >>> http://sqlblog.com/blogs/tibor_karaszi
    >>>
    >>>
    >>> "Stuart Ainsworth" wrote in message
    >>> news:5eae71ad-1c26-433a-a9de-3e152bfd23dcatx41g2000hsbdotgooglegroups.com...
    >>> On Apr 16, 6:32 pm, "tshad" wrote:
    >>>
    >>> Not easily in SQL; in most cases, formatting of data elements is
    >>> best
    >>> handled by your client application.
    >>>
    >>> Stu

    >
    >
    >>>> On Saturday, April 19, 2008 1:58 AM Stuart Ainsworth wrote:

    >
    >>>> Not easily in SQL; in most cases, formatting of data elements is
    >>>> best handled by your client application.
    >>>>
    >>>> Stu




+ Reply to Thread