Sunday, February 12, 2012

Convert decimal to VarChar without trailing 0's?

According to Books online...
"To remove trailing zeros from a result set when you convert from numeric or
decimal data to character data, use the value 128 for style."
So how come this code returns 10.2500000000? And more to the point how do I
get 10.25?
create table #test(
Val Decimal(19,10)
)
Insert into #test( Val ) Values ( 10.25 )
Select
Val,
Convert( VarChar, Val, 128 )
From #test
Any ideas?
Colin.Colin,
Try function STR instead.
select ltrim(str(cast(10.25 as Decimal(19,10)), 8, 2))
AMB
"Colin Dawson" wrote:

> According to Books online...
> "To remove trailing zeros from a result set when you convert from numeric
or
> decimal data to character data, use the value 128 for style."
> So how come this code returns 10.2500000000? And more to the point how do
I
> get 10.25?
> create table #test(
> Val Decimal(19,10)
> )
> Insert into #test( Val ) Values ( 10.25 )
> Select
> Val,
> Convert( VarChar, Val, 128 )
> From #test
>
> Any ideas?
> Colin.
>
>|||That works fine for the specific example, but if I have a number with a
different number of decimal places the results are not correct.
10.125 will get rounded, and this cannot be allowed to happen in the Medical
application that we're developing.
So far, the best solution that I've found is
Replace( RTrim( Replace( Replace( RTrim( Replace( Val, '0', ' ' ) ), ' ',
'0' ), '.', ' ' ) ), ' ', '.' ),
But this is rather CPU intensive. Looks like, I may be starting my own CLR
function library that contains all the stuff that MS missed from SQL2005.
Colin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:09C08477-DDA9-40C6-8CAF-208AC263B1F5@.microsoft.com...
> Colin,
> Try function STR instead.
> select ltrim(str(cast(10.25 as Decimal(19,10)), 8, 2))
>
> AMB
> "Colin Dawson" wrote:
>

No comments:

Post a Comment