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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Drop trailing zeros
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ingineu
Yak Posting Veteran

89 Posts

Posted - 02/26/2006 :  17:53:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 02/26/2006 :  18:12:35  Show Profile  Reply with Quote
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

Edited by - TG on 02/26/2006 18:14:07
Go to Top of Page

ingineu
Yak Posting Veteran

89 Posts

Posted - 02/27/2006 :  00:06:30  Show Profile  Reply with Quote
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?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 02/27/2006 :  10:40:03  Show Profile  Reply with Quote
>>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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 06/17/2006 :  08:22:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/17/2006 08:23:19
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 06/19/2006 :  04:40:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=47740

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000