Author |
Message |
Goldberg
Groupie
Joined: 15 Apr 2011
Location: Portugal
Online Status: Offline
Posts: 58
|
Topic: Split / Divide decimal numbers Posted: 04 Dec 2012 at 11:33pm |
Hello,
I need to toword (decimal) numbers (and write text between the unit and the decimal part), but I'm having trouble because on the decimal part of the number it will text it as <decimalnumber not in text> / 100, so I'm trying to split them and then toword it
For example:
I have 53,14 and I want to split it into 53 and into 14. I've tried truncate, but the decimal part splits as 0,14 and even if I multiply it by 100 it won't give me 14 (in this case it would give me 13,6).
So, my question is: How can I do it?
Thanks
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Dec 2012 at 3:35am |
what do you want 53.14 to end up looking like?
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 05 Dec 2012 at 4:22am |
you can convert to a string before, and manipulate it. But then again, I have the same question, what is your goal, what do you eventually want this 53.14 to output as?
If you just want '53 14' just do a (replace(totext(field),',',' '))
|
IP Logged |
|
Goldberg
Groupie
Joined: 15 Apr 2011
Location: Portugal
Online Status: Offline
Posts: 58
|
Posted: 05 Dec 2012 at 4:50am |
53,14 is just an example and it's meant to be, let's say, the worker's wage.
So, I want to make it toword and turn 53,14 into fifty three euros and fourteen cents.
Thanks
Edited by Goldberg - 05 Dec 2012 at 4:51am
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 05 Dec 2012 at 5:05am |
http://stackoverflow.com/questions/8432527/convert-number-to-words-first-second-third-and-so-on
this is not a simple task in my experience, but there are many resources I have used to accomplish this; this is one.
Edited by comatt1 - 05 Dec 2012 at 5:08am
|
IP Logged |
|
Goldberg
Groupie
Joined: 15 Apr 2011
Location: Portugal
Online Status: Offline
Posts: 58
|
Posted: 05 Dec 2012 at 5:58am |
Where should I put that SQL code? Crystal sintax won't support it.
Thanks
Edited by Goldberg - 05 Dec 2012 at 6:03am
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 05 Dec 2012 at 5:59am |
hmmm... go to
stackoverflow.com and just search for converting numbers to words.
an idea may be to split the two numbers (which could get complicated explaining since you Euros have all the commas/periods switched).
should grab the base number, no thousands separator;
left(replace(totext(@Data),'.',''), PatIndex(',', replace(totext(@Data),'.','') + '1') - 1)
should grab after the decimal;
right(replace(totext(@Data),'.',''), len(@data) - (PatIndex(',', replace(totext(@Data),'.','') )-1) )
FYI I replaced your thousands separator just for ease.
if you convert back to number on both values you should be able to convert both 'int' TOWORDS() without getting the fraction.
I haven't tested.
There are bound to be times you will encounter errors, with this method, but it will be easier than coding a whole new command without experience
---
Answer, separate your values,
take value to left of ',' or decimal whatever you call it
take value to right of ',' and then convert
may have to use left/right/instr/mid functions though.
if I typed it here would just be copying and pasting from another post though, so search on here for separating a string.
Edited by comatt1 - 05 Dec 2012 at 6:17am
|
IP Logged |
|
Goldberg
Groupie
Joined: 15 Apr 2011
Location: Portugal
Online Status: Offline
Posts: 58
|
Posted: 05 Dec 2012 at 6:04am |
Yes, sorry I edited it because I then searched on google and found it
|
IP Logged |
|
Goldberg
Groupie
Joined: 15 Apr 2011
Location: Portugal
Online Status: Offline
Posts: 58
|
Posted: 05 Dec 2012 at 10:56pm |
Using left(replace(totext(@Data),'.',''), PatIndex(',', replace(totext(@Data),'.','') + '1') - 1)
highlights "PatIndex" and says it expects a number, a hour, a boolean (...) Using right(replace(totext(@Data),'.',''), len(@data) - (PatIndex(',', replace(totext(@Data),'.','') )-1) )
says there is a ) missing, highlighting PatIndex, once again. But then I think your advice about using left/right/instr/mid functions solved my problem. So, to get the unit I truncated. @UNIT:
truncate({@valortotal}) and the output was 53. To get the decimal, and since it is always no less and no more than 2 digits, I used Right function and sinced it needed to be a string I totexted it @DECIMAL:
RIGHT(totext({@valortotal}),2) and the output was 14. And then I put them together (and needed to convert decimal back to number): @VALORTOTAL:
towords({@unit}) & " euros and " & towords(tonumber({@decimal})) & " cents" and the output was "fifty and three euros and fourteen cents". Thank you very much for your time, comatt1
& DBlank! :)
Edited by Goldberg - 05 Dec 2012 at 10:59pm
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 06 Dec 2012 at 2:52am |
Sorry about the patindex, think that will only work within SQL commands.
|
IP Logged |
|
|