remove .000 from the end of the decimal number

Hi experts,
I have a variable with type decimal(9, 3). Is it possible to return the
values without 0 at the end when it is unecessary ?
Examples:
1.000 should return 1
0.750 should return 0.75
0.500 should return 0.5
0.125 should return 0.125
Or should I write the function for that conversion?
Thank you!
Ramunas BalukonisDECLARe @.x AS decimal(9, 3)
SET @.x=1.000
SELECT CAST(@.x as numeric(9,0))
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1118133303.631018@.loger.vpmarket.int...
> Hi experts,
> I have a variable with type decimal(9, 3). Is it possible to return the
> values without 0 at the end when it is unecessary ?
> Examples:
> 1.000 should return 1
> 0.750 should return 0.75
> 0.500 should return 0.5
> 0.125 should return 0.125
> Or should I write the function for that conversion?
> Thank you!
> Ramunas Balukonis
>|||Roji
It won't work for 0.750 as the OP asked
Just for fun
declare @.w as decimal(9,3)
set @.w=0.125
select case when point=0 then cast(@.w as varchar(10))else
left(@.w,point-1)end
from
(
select charindex('0',cast(@.w as varchar(10)),
charindex('.',cast(@.w as varchar(10)),1)) as point
) as der
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:e9hoy4zaFHA.2884@.tk2msftngp13.phx.gbl...
> DECLARe @.x AS decimal(9, 3)
> SET @.x=1.000
> SELECT CAST(@.x as numeric(9,0))
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1118133303.631018@.loger.vpmarket.int...
>|||Uri,
try with 0.105
declare @.w as decimal(9,3)
set @.w=0.105
select case when point=0 then cast(@.w as varchar(10))else
left(@.w,point-1)end
from
(
select charindex('0',cast(@.w as varchar(10)),
charindex('.',cast(@.w as varchar(10)),1)) as point
) as der
returns 0.1
Ramunas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23DJECG0aFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Roji
> It won't work for 0.750 as the OP asked
> Just for fun
> declare @.w as decimal(9,3)
> set @.w=0.125
> select case when point=0 then cast(@.w as varchar(10))else
> left(@.w,point-1)end
> from
> (
> select charindex('0',cast(@.w as varchar(10)),
> charindex('.',cast(@.w as varchar(10)),1)) as point
> ) as der
>
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:e9hoy4zaFHA.2884@.tk2msftngp13.phx.gbl...
the
>|||Not very elegant but I believe this works:
DECLARE @.TestVar AS DECIMAL(9,3)
SET @.TestVar = 1.750
SELECT REPLACE( REPLACE( REPLACE( REPLACE(
CAST(@.TestVar AS VARCHAR(10)) + '#'
, '0#', '#'), '0#', '#'), '.0#', '#'), '#', '')
Need to add an extra inner replace for each additional decimal place
Yours
Alasdair Russell
"Ramunas Balukonis" wrote:

> Hi experts,
> I have a variable with type decimal(9, 3). Is it possible to return the
> values without 0 at the end when it is unecessary ?
> Examples:
> 1.000 should return 1
> 0.750 should return 0.75
> 0.500 should return 0.5
> 0.125 should return 0.125
> Or should I write the function for that conversion?
> Thank you!
> Ramunas Balukonis
>
>|||Ramunas,
This will just about do it:
replace(rtrim(replace(@.d,'0',' ')),' ','0')
It will return 1. instead of 1 with input 1.000, and it that is important,
use this:
replace(rtrim(replace(replace(rtrim(repl
ace(@.d,'0',' ')),' ','0'),'.','
')),' ','.')
Steve Kass
Drew University
Ramunas Balukonis wrote:

>Hi experts,
>I have a variable with type decimal(9, 3). Is it possible to return the
>values without 0 at the end when it is unecessary ?
>Examples:
>1.000 should return 1
>0.750 should return 0.75
>0.500 should return 0.5
>0.125 should return 0.125
>Or should I write the function for that conversion?
>Thank you!
>Ramunas Balukonis
>
>