Drop 0 value in decimal places

Question

Hi,

I want to ask if anybody know the query to drop the 0 value in decimal..

E.g : A field name percent have these values

Percent

770.00000000000000000000, 340.670000000000000000000, 96.00000000000000000000, 4400.56000000000000000000, 109.89000000000000000000, 109.00000000000000000000, 37.00000000000000000000,

Currently I'm using the query "select cast([Percent] as decimal(9,2)) as [Percent] from table" and will result in

Result

770.00, 340.67, 96.00, 4400.56, 109.89, 109.00, 37.00,

I want the result this actually:->

770, 340.67, 96, 4400.56, 109.89, 109, 37,

Answer 1

You could rather just cast to FLOAT.

Answer 2

You could use a combination of DECIMAL and FLOAT. Decimal first to round it down to 2 deciaml places, then float to remove unwanted 0's

e.g.

select cast(cast([Percent] as decimal(9,2)) AS FLOAT) as [Percent]

With the example of 340.69999999999999, first it round to 340.70, then it takes away the zero giving you 340.7. As with any rounding some precision will be lost.

Answer 3

This rather nasty TSQL might just do the job :

select 
  case 
    right(
       cast(cast([percent] as decimal(9,2)) as nvarchar(11))
    ,2)

    when '00' then cast(cast([percent] as int) as nvarchar(11)) as [percent]
    else cast(cast([percent] as decimal(9,2)) as nvarchar(11)) as [percent]

 end
from table

of course it is always returning a string, but that's inherent to your demands, you are looking for a representation for a value...

I think you should postpone that representation to where it makes more sense (report, datagrid?) and you have more tools (like string.format kinda tools) to do the job better.