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

From: Steve Kass (skass_at_drew.edu)
Date: 09/26/04 

Date: Sun, 26 Sep 2004 01:11:15 -0400

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.
>
>
>
>>.
>>
>>
>>

Relevant Pages