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

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.

>.
>
check my other reply.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You can try the following expression:
LEFT( c, LEN( c ) - PATINDEX( '%[1-9]%', REVERSE( c ) ) + 1 )
: where c is your column
Anith
|||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:
>.
>