"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