How can I remove trailinging zeros from decimal number in SQL Server? If I have as percentage field in database with value as 96.86, my query should return 96.86, but if the value is 96.00 then i need to fetch only 96. How can this be done?
Asked
Active
Viewed 4,064 times
0
-
They are called *trailing* zeroes (preceding would be like 0096). And why would you care? The numerical value doesn't change! – Hans Kesting Jun 04 '18 at 11:05
-
Formatting data for display purposes is a job best done in the presentation layer rather than T-SQL. – Dan Guzman Jun 04 '18 at 11:09
-
I have edited from preceding to trailing, sorry my bad.. – Taufik Shaikh Jun 04 '18 at 11:27
-
Does this answer your question? [Remove trailing zeros from decimal in SQL Server](https://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server) – JumpingJezza Feb 24 '22 at 03:32
2 Answers
1
i think you should use
select cast(CAST(25.00 as decimal(18,5)) as float)
it return 25 but you cast
select cast(CAST(25.23 as decimal(18,5)) as float)
it return 25.23
Manish Singh
- 934
- 1
- 12
- 27
0
You can convert to a string and then trim the trailing 0 characters. Unfortunately, SQL Server does not have really powerful string functions. The following does do what you want:
select (case when nn like '%.' then replace(nn, '.', '') else nn end)
from (select 96.86 as n union all select 96) t cross apply
(values (ltrim(rtrim(replace(cast(n as varchar(255)), '0', ' '))))) v(nn)
Gordon Linoff
- 1,242,037
- 58
- 646
- 786