| Author |
Message
|
| gbbailey |
 Posted: Wed Nov 22, 2006 3:35 am Post subject: [SOLVED] Removing trailing 0 when casting float to string |
 |
|
Apprentice
Joined: 12 May 2006
Posts: 27
Location: London, UK
|
I'm casting a float to a string as part of a SET statement.
The requirement is to strip trailing zeros after the decimal point.
| Quote: |
112.350 -> 112.35
23570.00 -> 23570
|
Is there a neat way to do this?
Kind regards,
Greg
Last edited by gbbailey on Fri Nov 24, 2006 2:35 am; edited 1 time in total |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Nov 22, 2006 3:47 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Look up the TRIM function
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| gbbailey |
| Posted: Wed Nov 22, 2006 3:53 am Post subject: |
|
|
Apprentice
Joined: 12 May 2006
Posts: 27
Location: London, UK
|
I've taken a look at trim but it doesn't look neat.
I guess you'd have something like:
| Code: |
IF aFloat contain '.' THEN
SET bFloat = TRIM (TRAILING '0' from aFloat);
SET aChar = TRIM( TRAILING '.' FROM bFloat);
ELSE
SET aChar = aFloat
END IF; |
Is there any way to do this as a cast? |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Nov 22, 2006 4:31 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If you are on V6 you should be able to do this with a FORMAT clause on a CAST
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| gbbailey |
| Posted: Wed Nov 22, 2006 4:33 am Post subject: |
|
|
Apprentice
Joined: 12 May 2006
Posts: 27
Location: London, UK
|
I looked at that too and that's where I got a bit stuck. Perhaps I should have mentioned this in the original post.
The FORMAT stuff seems to all be about fixed decimal places, significant figures etc. which is not quite what I want. My big hope is that I am mistaken on this though and that there is some way to do this. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Nov 22, 2006 4:42 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You're already casting it to a string - you could look at using POSITION and SUBSTRING to find the first non-zero character from the right after the decimal and then remove it.
You'd probably have to iterate over POSITION to find the first non-zero, though - I guess I might even just iterate until I stop finding a 0.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Nov 22, 2006 5:51 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Try:
| Code: |
| SET aChar = CAST (aFloat AS CHARACTER FORMAT '0.##'); |
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| gbbailey |
| Posted: Wed Nov 22, 2006 6:57 am Post subject: |
|
|
Apprentice
Joined: 12 May 2006
Posts: 27
Location: London, UK
|
My understanding is that the # is only relevant for leading 0 and not trailing 0.
I shall give it a go though. I guess I'd have to wrap it in a TRIM as well to get rid of any trailing '.' that may result. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Nov 22, 2006 7:24 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
No you will not need the trim, and you are mistaken about the #. I tested this code and the . is not present in your second case. Assuming your requirements are as you stated if your original post, then this works.
The code I used was (testing with DECIMAL and FLOAT)
| Code: |
SET OutputRoot.MQMD = InputRoot.MQMD;
DECLARE aFloat FLOAT 112.350;
DECLARE aFloat2 FLOAT 23570.00;
DECLARE aDEC DECIMAL 112.350;
DECLARE aDEC2 DECIMAL 23570.00;
SET OutputRoot.XMLNSC.Top.aFLOATBefore = aFloat;
SET OutputRoot.XMLNSC.Top.aFLOATAfter = CAST (aFloat AS CHARACTER FORMAT '0.##');
SET OutputRoot.XMLNSC.Top.aFLOAT2Before = aFloat2;
SET OutputRoot.XMLNSC.Top.aFLOAT2After = CAST (aFloat2 AS CHARACTER FORMAT '0.##');
SET OutputRoot.XMLNSC.Top.aDECBefore = aDEC;
SET OutputRoot.XMLNSC.Top.aDECAfter = CAST (aDEC AS CHARACTER FORMAT '0.##');
SET OutputRoot.XMLNSC.Top.aDEC2Before = aDEC2;
SET OutputRoot.XMLNSC.Top.aDEC2After = CAST (aDEC2 AS CHARACTER FORMAT '0.##'); |
The output from this was:
| Code: |
<Top>
<aFLOATBefore>1.1235E+2</aFLOATBefore>
<aFLOATAfter>112.35</aFLOATAfter>
<aFLOAT2Before>2.357E+4</aFLOAT2Before>
<aFLOAT2After>23570</aFLOAT2After>
<aDECBefore>112.350</aDECBefore>
<aDECAfter>112.35</aDECAfter>
<aDEC2Before>23570.00</aDEC2Before>
<aDEC2After>23570</aDEC2After>
</Top> |
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| gbbailey |
| Posted: Fri Nov 24, 2006 2:37 am Post subject: |
|
|
Apprentice
Joined: 12 May 2006
Posts: 27
Location: London, UK
|
Thank you very much. That CAST works a treat.
The documentation seemed fascinated with the word "leading" when referring to # though - and I am presently suprised by the behaviour with the decimal point. I should be more courageous in my experimentation. |
|
| Back to top |
|
|
|
|
