Re: how to suppress zeroes after decimal and whole no. should remain same

From: hari (anonymous_at_discussions.microsoft.com)
Date: 09/28/04

  • Next message: Steve Kass: "Re: Number to Date" 
    Date: Mon, 27 Sep 2004 18:43:31 -0700

    thanks Steve,
    it's working fine for removing zeroes and formating it
    with commas. i changed it with combining ur previous and
    current answers.

    rgds,
    hari.

    >-----Original Message-----
    >Here is another option:
    >
    >select
    > parsename(c1,2)
    >+ coalesce('.'+reverse(0+nullif(reverse(parsename
    (c1,1)),0)),'')
    >from test
    >
    >SK
    >
    >hari wrote:
    >
    >>hi ,
    >>
    >> but it is not working for whole number like ex:11120
    >> it is fetching it as 11120. (with point at the end)
    >> how to do that.
    >>
    >>thanks,
    >>
    >>hari.
    >>
    >>
    >>>-----Original Message-----
    >>>see following example:
    >>>
    >>>drop table test
    >>>create table test(c1 decimal (15,5))
    >>>insert into test values (3.567000)
    >>>insert into test values (232233.567000)
    >>>insert into test values (3.567)
    >>>
    >>>query:
    >>>select c1,reverse(substring(reverse(cast(c1 as varchar
    >>>
    >>>
    >>(25))) ,
    >>
    >>
    >>>patindex('%[^0]%', reverse(cast(c1 as varchar(25)))) ,
    >>>len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
    >>>
    >>>
    >>reverse(cast(c1 as
    >>
    >>
    >>>varchar(25)))) - 1)
    >>>)) 'no_zeros'
    >>>
    >>>
    >>>from test
    >>
    >>
    >>>--
    >>>Vishal Parkar
    >>>vgparkar@yahoo.co.in | vgparkar@hotmail.com
    >>>
    >>>hi,
    >>> i have a sql table field price and datatype is
    >>>decimal 13(20,6).
    >>> when i insert values to this field, values are being
    >>>inserted correctly. i.e. 13.45 inserted as 13.45 and
    >>>145.653 inserted as 145.653 only.
    >>>But while fetching only the values are coming as
    >>>13.450000,
    >>>145.653000, because the datatype is decimal 13(20,6)
    >>>with
    >>>6 decimals. but i want 13.45, 145.653 as in the table.
    >>>
    >>>How to suppress the unwanted zeroes at the end of those
    >>>numbers.
    >>>any help.
    >>>
    >>>
    >>
    >>thanks,
    >>hari.
    >>
    >>
    >>
    >>>.
    >>>
    >>>
    >>>
    >.
    >

  • Next message: Steve Kass: "Re: Number to Date"

    Relevant Pages

    • Re: format a numeric values with commas
      ... it's working fine for removing zeroes and formating it ... >hari wrote: ...
      (microsoft.public.sqlserver.mseq)
    • Re: Calculating Date difference in 2 ways
      ... My doubt is that inspite of the same formulas and with both A1 and B1 having ... no previous formating, what causes excel to interpret these 2 approaches as ... > Hari wrote: ... >> Regards, ...
      (microsoft.public.excel.misc)