Re: how to suppress zeroes after decimal point at the end in a value

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 09/23/04 

Date: Fri, 24 Sep 2004 00:23:19 +0530

try:

select c1, reverse(
             case when substring(substring(reverse(cast(c1 as varchar(25)))
,
                       patindex('%[^0]%', reverse(cast(c1 as varchar(25))))
,
                       len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1)) ,1,1) = '.'
                   then
   substring(reverse(cast(c1 as varchar(25))) ,
                        patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) + 1 ,
                        len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 2))
                   else
   substring(reverse(cast(c1 as varchar(25))) ,
                        patindex('%[^0]%', reverse(cast(c1 as
varchar(25)))) ,
                        len(cast(c1 as varchar(25))) - (patindex('%[^0]%',
reverse(cast(c1 as varchar(25)))) - 1))
                   end)
'no_zeros'
from test 

-- 
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com
"hari" <anonymous@discussions.microsoft.com> wrote in message
news:207601c4a145$b5262d90$a601280a@phx.gbl...
> hi thanks,
>
>  but it is not working for whole number like ex:11120
>  it is giving it as 11120.  (with point at the end)
>  how to do that.
>
> thanks,
>
> hari.
> >-----Original Message-----
> >see following example:
> >
> >drop table test
> >create table test(c1 decimal (15,5))
> >insert into test values (3.567000)
> >insert into test values (232233.567000)
> >insert into test values (3.567)
> >
> >query:
> >select c1,reverse(substring(reverse(cast(c1 as varchar
> (25))) ,
> >patindex('%[^0]%',  reverse(cast(c1 as varchar(25)))) ,
> >len(cast(c1 as varchar(25))) -  (patindex('%[^0]%',
> reverse(cast(c1 as
> >varchar(25)))) - 1)
> >)) 'no_zeros'
> >from test
> >
> >-- 
> >Vishal Parkar
> >vgparkar@yahoo.co.in | vgparkar@hotmail.com
> >
> >
> >.
> >