1

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'
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
spspli
  • 3,128
  • 11
  • 48
  • 75
  • The three numbers are 56.1234567800 – spspli May 21 '11 at 02:51
  • 5
    the question "why would you want that" springs to mind.... – Mitch Wheat May 21 '11 at 02:55
  • 2
    Seconding Mitch's response, formatting is for the client application - and converting numeric values to strings puts a hold on any further data manipulation unless you convert them back again. – Will A May 21 '11 at 02:56
  • possible duplicate of [Remove trailing zeros from decimal in SQL Server](http://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server) – OMG Ponies May 21 '11 at 05:25
  • I want to do this because in my front-application I want to got the data in database as string(not float), and the string should be exactly the same as what it is as numeric in the database but without all tailing '0's. My front-application is a Delphi application. It executes a procedure and then read the fields selected in the procedure. – spspli May 21 '11 at 18:26
  • I have tried in Delphi the property AsString, it does what I want except for very small number like 0.0000001000, it returns 1E-7 other than 0.0000001. So I think maybe I need to let the procedure take this job do the convention before my Delphi read it. – spspli May 21 '11 at 18:27

2 Answers2

4

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.
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0
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')
Zoe
  • 27,060
  • 21
  • 118
  • 148
Pandidurai
  • 409
  • 4
  • 7