Drop trailing zeros - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Drop trailing zeros

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-26 : 17:53:31

I have a Numeric(18,5) field that is concatenated along with some other fields for Error display purposes. How do I drop the trailing zero's for my output?

eg. UnitRate = 5.455
CAST(UnitRate AS varchar(18)) ... would give me 5.45500

I would like it to display as 5.455

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-26 : 18:12:35

Are you saying that if the UnitRate is 5.400 you'd want '5.4' but if it's 5.4552500 you'd want '5.45525'?
I guess we're talking about a flexable precision. If that's the case you might try converting to float then to varchar although you may loose some precision in some cases this way.


select CAST(convert(float,UnitRate) AS varchar(18))
from	(select convert(numeric(18,5), 5.455) as UnitRate union
	select  convert(numeric(18,5), 5.4) union
	select  convert(numeric(18,5), 5.455025)) a

EDIT:
I keep forgetting to include the output:
------------------ 
5.4
5.455
5.45503

Be One with the Optimizer
TG

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-27 : 00:06:30

Well, I would like to display at least 2 decimal places and drop the trailing zeros. Kind of like the FORMAT statement in Access where you can specify the output as '0.00###'. Is there something like this available in SQL?
When you mention converting to float, and thereby maybe losing some precision, I would of course want to display the correct rate, would the float format change the value?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-27 : 10:40:03

>>Kind of like the FORMAT statement in Access where you can specify the output as '0.00###'. Is there something like this available in SQL?

No. Since MS Access is both a database as well as a presentation layer, it provided that as a formatted display function. What are you currently using as your presentation software. Most will allow for flexible formatting of numeric output.

>>would the float format change the value?
As you can see in my sample output (above) 5.455025 was rounded up to 5.45503 when converted to float. As stated in Books Online about Float and Real datatypes:

quote:
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-17 : 08:22:56

[code]-- prepare test data
declare @num table (i varchar(50))

insert @num
select '134.000' union all
select '1.1200' union all
select '100.00' union all
select '69' union all
select '13.' union all
select '123456789.9876543210000000000000000000000000' union all
select '0.0200'

-- do the work
SELECT i,
CASE WHEN PATINDEX('%[1-9]%', REVERSE(i)) < PATINDEX('%.%', REVERSE(i)) THEN LEFT(i, LEN(i) - PATINDEX('%[1-9]%', REVERSE(i)) + 1) ELSE LEFT(i, LEN(i) - PATINDEX('%.%', REVERSE(i))) END 'Converted'
FROM @num[/code]
Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-19 : 04:40:25

Also refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=47740

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources