SEO

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?

"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

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?

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

Thanks for the help on this matter.

I agree with both of you. The formatting SHOULD be done on the client
when possible but in this case I have no choice. The logic will be
used in a function on the server when combining several fields to make
a new one.

ShShapourian@hotmail.com (Shervin Shapourian) wrote in message news:<4eaef17a.0309171902.1d659b9d@posting.google. com>...
> 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
>
>
>

create table #t (c decimal(10,6))
insert into #t values (45.34)
insert into #t values (27.7)
insert into #t values (55)
insert into #t values (101.1)

select c,
replace(rtrim(replace(
replace(rtrim(replace(
cast(c as varchar(12))
,'0',' ')),' ','0')
,'.',' ')),' ','.')
from #t

drop table #t

Gert-Jan

Jason wrote:
>
> 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?