Hi Jason,

I'm not sure if you can find a function in TSQL that does what you
want in this case. But you can use a bunch of string functions to do
the trick, like this:


select rtrim(
replace(
replace(
rtrim(
replace(
cast(DecField as char(11)),
'0',
' '
)
),
' ',
'0'
) + ' ',
'. ',
''
)
)
from YourTable

Let me explain what it does.
Assume you have these decimal values: 43.035000 and 55.000000

1. Convert decimal value into string
x = cast(DecField as char(11))
'43.035000 '
'55.000000 '

2. Replace 0's with space
x = replace(x, '0', ' ')
'43. 35 '
'55. '

3. Trim the trailing spaces
x = RTrim(x)
'43. 35'
'55.'

4. Replace spaces with 0
x = replace(x, ' ', '0')
'43.035'
'55.'

As you see by now all noninteger values are ready, but still we need
to get rid of decimal point appearing at the end of integer values.

5. Add a single space to the end
x = x + ' '
'43.035 '
'55. '

6. Replace '. ' with an empty string
x = replace(x, '. ', '')
'43.035 '
'55'

7. Finally get rid of remaining spaces
x = RTrim(x)
'43.035'
'55'


Well, this works fine but it's not the most efficient to do it.
Depending on size of your table and the number of decimal fields you
want to show, it might take a while. I personally prefer to perform
such cosmetic transformations in front-end application at the time you
display the values. Of course it depends on your programming
environment and its features.

Good luck,
Shervin



JayCallas@hotmail.com (Jason) wrote in message news:<f01a7c89.0309170706.5bda9f3c@posting.google. com>...
> 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?