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?
4
-
They are called trailing zeroes (preceding would be like 0096). And why would you care? The numerical value doesn't change!Hans Kesting– Hans Kesting2018-06-04 11:05:56 +00:00Commented Jun 4, 2018 at 11:05
-
Formatting data for display purposes is a job best done in the presentation layer rather than T-SQL.Dan Guzman– Dan Guzman2018-06-04 11:09:34 +00:00Commented Jun 4, 2018 at 11:09
-
I have edited from preceding to trailing, sorry my bad..Taufik Shaikh– Taufik Shaikh2018-06-04 11:27:00 +00:00Commented Jun 4, 2018 at 11:27
-
Does this answer your question? Remove trailing zeros from decimal in SQL ServerJumpingJezza– JumpingJezza2022-02-24 03:32:33 +00:00Commented Feb 24, 2022 at 3:32
Add a comment
|
2 Answers
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
Comments
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)
