Hi Simon,

This script removes all the 0's, not just trailing ones.

Shervin

"Simon Hayes" <sql@hayes.ch> wrote in message news:<3f68b36a$1_4@news.bluewin.ch>...
> "Jason" <JayCallas@hotmail.com> wrote in message
> news:f01a7c89.0309170706.5bda9f3c@posting.google.c om...
> > I have a column defined as DECIMAL(10,6). I want to display it as a
> > string but I do not want the trailing zeros. I cannot seem to get CAST
> > or CONVERT or STR to exclude the zeros.
> >
> > Examples:
> > 45.340000 --> 45.34
> > 27.700000 --> 27.7
> > 55.000000 --> 55
> >
> > Is there a function that will do this or do I need to write my own?
>
> It's usually best to do this formatting in the client, rather than the
> database layer - client tools will have much better formatting options, and
> they allow for changing the formatting dynamically without having to
> resubmit the query. But here's one way to do it:
>
> select case
> when right(replace(convert(varchar, MyColumn), '0', ''), 1) = '.'
> then replace(replace(convert(varchar, MyColumn), '0', ''), '.', '')
> else replace(convert(varchar, MyColumn), '0', '')
> end
> from dbo.MyTable
>
> Simon