Value 0 in decimal places

Question

I want to ask if anyone knows that the request rejects the value 0 in decimal format.

For example: the percentage field name has the following meanings

Percent

+770.0000000000000000000000, +340.670000000000000000000, +96.00000000000000000000, +4400.56000000000000000000, +109.89000000000000000000, +109.00000000000000000000, 37.00000000000000000000,

I am currently using the query "select cast ([Percent] as decimal (9.2)) as [Percent] from the table" and will result in

Result

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

I want to get the result: →

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

Answer 1

You can use a combination of DECIMAL and FLOAT. Decimal first round to 2 decamen places, then swim to remove unnecessary 0

eg.

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

For example, 340.69999999999999 first, it is rounded to 340.70, then it removes zero, giving you 340.7. As with any rounding, some accuracy will be lost.

Answer 2

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 always returns a string, but according to your requirements you are looking for a representation for the value ...

I think you should postpone this view to where it makes more sense (report, datagrid?), And you have more tools (like string.format tools) to do the job better.

Answer 3

Most likely, you just apply FLOAT.

Answer 4

You can use the CONVERT function twice, once, to discard 0 , converting it to float and once, to convert it to varchar using style 128

 DECLARE @Sample AS TABLE ( SomeNumber DECIMAL(26, 12) ) INSERT INTO @Sample VALUES ( 770.00000000000000000000 ) , ( 340.670000000000000000000 ) , ( 96.00000000000000000000 ) , ( 4400.56000000000000000000 ) , ( 109.89000000000000000000 ) , ( 109.00000000000000000000 ) , ( 37.00000000000000000000 ) SELECT CONVERT(VARCHAR(25), CONVERT(FLOAT, SomeNumber), 128) AS NoZeros FROM @Sample