Is there a way to trim tailing 0 in decimals and return as string in sql?

My database has 开发者_如何学运维a field "TestNumber numeric(27,10)", it has the value like those numbers:

56.1234567800
 0.0000000010
 1.0010100000

Convert(Varchar(28),TestNumber) will convert the above TestNumber to VARCHAR.

Is there any function in sql (I use sql server 2008R2) to trim the tailing '0's in decimal places so I actually could get those strings:

'56.12345678'
 '0.000000001'
 '1.00101'

Is there any function in sql

No not that I know of. You can replace all '0' with ' ', use rtrim and put the '0' back again.

with T(N) as
(
 select 56.1234567800 union all
 select 0.0000000010 union all
 select 1.0010100000 union all
 select 100
)

select
  replace(rtrim(replace(convert(varchar(28), T.N), '0', ' ')), ' ','0') as N
from T

Result:

N
------------
56.12345678
0.000000001
1.00101
100.

SELECT FORMAT(CAST(99.0000    AS DECIMAL(18,7)), 'g18')

SELECT FORMAT(CAST(0.0009856  AS DECIMAL(18,5)), 'g10')

SELECT FORMAT(CAST(2.0        AS DECIMAL(18,7)), 'g15')